Dividing comma separated strings into columns

oracleoracle-10g

I have a problem with dividing comma separated values. I have 2 columns with comma separated values, for ex:

ID        Name 
1,2,3     Ab,cd,ef

I want the columns divided as:

Id  Name
1   ab
2   cd
3   ef

I used:

 xmltable('r/c' passing xmltype('<r><c>' || replace(ID,',','</c><c>') || '</c></r>')
          columns ID_NEW varchar2(400) path '.') <br/>

Output was:

ID      Name 
1       ab,cd,ef
2       ab,cd,ef
3       ab,cd,ef

I'm using Oracle 10g.

Best Answer

Is this what you're looking for? I'm using regexp_substr

with test as
(select '1,2,3' col1, 'Ab,cd,ef' col2 from dual)
  select regexp_substr(col1, '[^,]+', 1, rownum) result1, regexp_substr(col2, '[^,]+', 1, rownum) result2
  from test
  connect by level <= length(regexp_replace(col1, '[^,]+')) + 1;

This is the SQL Fiddle.

Related Question