Divide data in a single column with symbol of '|' to multiple columns in Oracle SQL

Question

I have a column value of below

MERGE|WorkRelationship|||2020/12/31|RESIGNATION|Y||XYZ Limited|12345A

I need a sql to divide into multiple column

Col1 - MERGE
Col2 - WorkRelationship
col3 - NULL
col4 - NULL
Col5 - 2020/12/31
Col6 - RESIGNATION
Col7 -NULL

Like wise for entire value.

Can you please help me on this.

Do a little searching on parsing delimited strings into columns, this has been answered many times. — Gary_W, Jul 15 '20 at 01:44
Does this answer your question? [Split comma separated values to columns in Oracle](https://stackoverflow.com/questions/31464275/split-comma-separated-values-to-columns-in-oracle) — Gary_W, Jul 15 '20 at 01:47
If you are interested in the separate parts, why do you store these as one concatenated string in your table? I suggest you change your table design. — Thorsten Kettner, Jul 15 '20 at 06:26

GMB · Answer 1 · 2020-07-15T01:48:51.137

One option is regexp_substr(). Here is one approach that takes in account the possibility of empty slots in the delimited string:

select
    regexp_substr(col, '([^|]*)(\||$)', 1, 1, null, 1) col1,
    regexp_substr(col, '([^|]*)(\||$)', 1, 2, null, 1) col2,
    regexp_substr(col, '([^|]*)(\||$)', 1, 3, null, 1) col3,
    regexp_substr(col, '([^|]*)(\||$)', 1, 4, null, 1) col4,
    regexp_substr(col, '([^|]*)(\||$)', 1, 5, null, 1) col5,
    regexp_substr(col, '([^|]*)(\||$)', 1, 6, null, 1) col6,
    regexp_substr(col, '([^|]*)(\||$)', 1, 7, null, 1) col7
from mytable

Demo on DB Fiddle:

with mytable as (select 'MERGE|WorkRelationship|||2020/12/31|RESIGNATION|Y||XYZ Limited|12345A' col from dual)
select
    regexp_substr(col, '([^|]*)(\||$)', 1, 1, '', 1) col1,
    regexp_substr(col, '([^|]*)(\||$)', 1, 2, '', 1) col2,
    regexp_substr(col, '([^|]*)(\||$)', 1, 3, '', 1) col3,
    regexp_substr(col, '([^|]*)(\||$)', 1, 4, '', 1) col4,
    regexp_substr(col, '([^|]*)(\||$)', 1, 5, '', 1) col5,
    regexp_substr(col, '([^|]*)(\||$)', 1, 6, '', 1) col6,
    regexp_substr(col, '([^|]*)(\||$)', 1, 7, '', 1) col7
from mytable

COL1  | COL2             | COL3 | COL4 | COL5       | COL6        | COL7
:---- | :--------------- | :--- | :--- | :--------- | :---------- | :---
MERGE | WorkRelationship | null | null | 2020/12/31 | RESIGNATION | Y

score 1 · Answer 2 · answered Jul 15 '20 at 05:24

If there's not just one string you're splitting, and if it contains variable number of substrings, this might help:

SQL> with test (col) as
  2    (select 'MERGE|WorkRelationship|||2020/12/31|RESIGNATION|Y||XYZ Limited|12345A' from dual
  3     union all
  4     select 'Little|Foot' from dual
  5    )
  6  select rtrim(regexp_substr(col, '[^|]*\|?', 1, column_value), '|') val
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= regexp_count(col, '\|') + 1
 10                       ) as sys.odcinumberlist))
 11  order by col, column_value;

VAL
------------------------------
Little
Foot
MERGE
WorkRelationship


2020/12/31
RESIGNATION
Y

XYZ Limited
12345A

12 rows selected.

SQL>

Divide data in a single column with symbol of '|' to multiple columns in Oracle SQL

2 Answers2