I have comma delimited data in multiple columns.
Like this:
id c1 c2
100 A,B 1,2
101 C,D 3
102 E 4,5
What I need to get is:
id c1 c2
100 A 1
100 B 2
101 C 3
101 D
102 E 4
102 5
Any help would be greatly appreciated.
The trick is to find out which column on the current row has the most delimiters and add one to it and that's how many times you'll need to "loop"
via the connect by. The "level" variable that comes along with connect by contains the count. So the union counts the delimiters on the current row, the MAX() gets the largest number, then the connect by "loops" that many times. Note this form of the regex handles NULL list elements, where the more commonly seen form of '[^,]*' used for parsing strings does not. See here for more info on that: https://stackoverflow.com/a/31464699/2543416.
-- This CTE sets up the data with variable list elements
with tbl (id, c1, c2) as (
select 100, 'A,B', '1,2' from dual union all
select 101, 'C,D', '3' from dual union all
select 102, 'E', '4,5' from dual union all
select 103, NULL, '7,8,9' from dual union all
select 104, 'F,G,,I,J', '10,11,12,13,14' from dual -- NULL list element
)
select id,
-- Don't use '[^,]*' it doesn't handle NULL list elements
regexp_substr(c1, '(.*?)(,|$)', 1, level, NULL, 1) part1,
regexp_substr(c2, '(.*?)(,|$)', 1, level, NULL, 1) part2
from tbl a
connect by level <= (select max(count)+1
from (select regexp_count(c1, ',') as count from tbl b where a.id = b.id union
select regexp_count(c2, ',') as count from tbl b where a.id = b.id )
)
and prior id = id
and prior sys_guid() is not null;
ID PART1 PART2
------ -------- --------------
100 A 1
100 B 2
101 C 3
101 D
102 E 4
102 5
103 7
103 8
103 9
104 F 10
104 G 11
104 12
104 I 13
104 J 14
14 rows selected.
