sqloracle-database

Split varchar into separate columns

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?

Solution

  • 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: