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
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
- 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
Works like charm
ReplyDeleteWorks like charm
ReplyDeleteawesome compilation!!! was stuck on conversion for Comma Separated String To Rows
ReplyDeletefor couple of days...finally job done in 2 mins with your code...thanks a bunch!!!
Thanks
ReplyDeleteworked for me!
ReplyDeleteIt 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