sql - Split varchar into separate columns in Oracle

Question

I'm in a bit of a pickle: I've been asked to take in comments starting with a specific string from a database, and separate the result into separate columns.

For example -- if a returned value is this:

COLUMN_ONE
--------------------
'D7ERROR username'

The return needs to be:

COL_ONE    COL_TWO
--------------------
D7ERROR   username   

Is it even possible to define columns once the result set has been structured just for the sake of splitting a string into two?

Answer 1

Depends on the consistency of the data - assuming a single space is the separator between what you want to appear in column one vs two:

SELECT SUBSTR(t.column_one, 1, INSTR(t.column_one, ' ')-1) AS col_one,
       SUBSTR(t.column_one, INSTR(t.column_one, ' ')+1) AS col_two
  FROM YOUR_TABLE t

Oracle 10g+ has regex support, allowing more flexibility depending on the situation you need to solve. It also has a regex substring method...

Reference:

• SUBSTR
• INSTR