0

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.

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

2 Answers 2

1

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.

1
  • +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 Dec 18, 2013 at 12:23
0

11g - the same with regexp_count()

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 <= regexp_count(col1, ',') + 1; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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