Oracle: Comma Separated String To Rows 10g Onwards

One simple way for single string
with t as
(select '01,05,10,15,20,25,30,35,50,75,23,03,04,00,00,00,00,00' txt from dual)
select regexp_substr ( txt, '[^,]+', 1, level) data from t CONNECT BY level <= length (txt) - length (replace (txt, ',')) + 1

For Multiple Rows with comma Separated values:
with mytable as
(
    select 'nimish' col,  '1,2,3,4,5' txt from dual
    union
    select 'garg' col,  '6,7,8,9' txt from dual
)
select    
    mytable.col,  
    regexp_substr ( mytable.txt, '[^,]+', 1, n) as num
from  
    mytable,
    (
        select  
            level n
        from  
            (
                select
                    max ( length (txt) - length (replace (txt, ',')))    as max_commas
                from  
                    mytable

            )
        connect by level <= 1 + max_commas
    ) ctr
where
    ctr.n    <= 1 + length (txt) - length (replace (txt, ','))
order by
    mytable.col,
    ctr.n

OUTPUT
    

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 11g
- Oracle: Nth Record from Comma Separated String
- Oracle: Department wise Employee Ids Comma Separated
- Oracle: Comma Separated String To Rows

6 comments:

  1. awesome compilation!!! was stuck on conversion for Comma Separated String To Rows
    for couple of days...finally job done in 2 mins with your code...thanks a bunch!!!

    ReplyDelete
  2. It works well when i run for few records . When i execute for around 3000 records , it just getting hanged, forever to complete. I couldnt see the results. Can anyone help me on this.

    ReplyDelete