Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
xmltable('r/c' passing xmltype('<r><c>' || replace(ID,',','</c><c>') || '</c></r>') columns ID_NEW varchar2(400) path '.') –  Arun Sep 15 '13 at 22:07
    
Sorry I used tha above query to get output but its not working as expected –  Arun Sep 15 '13 at 22:09
add comment

1 Answer

is this what u're looking for? im 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;

sorry if im wrong, im still newbie here :)

this is the SQL Fiddle

share|improve this answer
    
+1 This is definitely on the right path. Your SQLFiddle creates a table then doesn't use it btw. The main issue seems to me to be that it is likely that the OPs tabel has more than one row in it sqlfiddle.com/#!4/d4e3f1/1 –  Jack Douglas Dec 18 '13 at 12:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.