Oracle: Comma Separated String To Rows 11g

FOR NUMBERS

with mytable as
(
    select 'smith' ename,  '1,2,3,4,5' mytext from dual
    union
    select 'ward' ename,  '2,4,6,7' mytext from dual
)
select ename,(column_value).getnumberval()
from mytable,  xmltable(mytext)

OUTPUT















FOR STRINGS USE (column_value).getstringval()

with mytable as
(
    select 'smith' ename,  '"a","b","c","d","e"' mytext from dual
    union
    select 'ward' ename,  '"b","d","f","g"' mytext from dual
)
select ename,(column_value).getstringval()
from mytable,  xmltable(mytext)


Related Links:
- Function to Compare two Comma Separated Strings in Oracle
- Oracle: New String Aggregation Techniques
- Oracle: Difference between wm_concat and ListAgg
- Oracle: Comma Separated String To Rows 10g Onwards
- Oracle: Nth Record from Comma Separated String
- Oracle: Department wise Employee Ids Comma Separated
- Oracle: Comma Separated String To Rows

12 comments:

  1. doesnt work!!!! at least in 11 g

    ReplyDelete
  2. Many, many thanks for this.
    You saved me a lot of time and work.

    ReplyDelete
  3. Many thanks from me, as well. We've been using custom functions and SQL types for this, and it's great to find a simpler method, especially one that uses nothing but Oracle supplied functionality.

    Russ Whiteman

    ReplyDelete
  4. Great! Thanks so much. Simple and works better and faster than other solution like connect by etc...

    ReplyDelete
  5. Doesn't work for me either..... tried like below

    with mytable as
    (
    select STATE, city from TABLEA where state ='AP'
    )
    select STATE,(column_value).getstringval()
    from mytable, xmltable(city);

    ============
    ORA-19112: error raised during evaluation:
    XVM-01002: [XPDY0002] Dynamic context component 'context item' has no value
    19112. 00000 - "error raised during evaluation: %s"
    *Cause: The error function was called during evaluation of the XQuery expression.
    *Action: Check the detailed error message for the possible causes.

    ReplyDelete
    Replies
    1. xmltable(city) should contain comma separated value in following format '"a","b","c","d","e"'

      Delete
  6. It worked...thanks a lot...I was using the traditional method of regular exp + connect by...but this seems to be easy, simple and probably faster....thanks a lot !! It helps.

    ReplyDelete
  7. What if your data only has double quotes in it or say '&' then its giving error
    ORA-19112: error raised during evaluation:
    XVM-01002: [XPDY0002] Dynamic context component 'context item' has no value
    19112. 00000 - "error raised during evaluation: %s"
    *Cause: The error function was called during evaluation of the XQuery expression.
    *Action: Check the detailed error message for the possible causes.

    is there an alternative?? I tried regexp using connect by but its huge data and taking much time

    ReplyDelete
  8. SQL> select * from data1;

    smith 1,2,3,4
    gappu 5,6,7,8

    SQL> select distinct name, regexp_substr(data,'[0-9]{1}',1,level) as dt from data1 connect by level<=regexp_count(data,',')+1 order by dt;

    smith 1
    smith 2
    smith 3
    smith 4
    gappu 5
    gappu 6
    gappu 7
    gappu 8

    8 rows selected.

    ReplyDelete
  9. I hope you are doing well as I write this. I appreciate you bringing up "Oracle: Comma Separated String to Rows 11g" with an emphasis on Connectors-1. It can be difficult to work with comma-separated strings in Oracle, and using connectors like Connectors-1 increases both the complexity and efficiency of the process.

    ReplyDelete