I have tried below:
with rws as ( select 'split,into,rows' str from dual)
select regexp_substr ( str,'[^,]+',1,level) value
from rws
connect by level <= length ( str ) - length ( replace ( str, ',' ) ) + 1;
Value:
split
into
rows
But if there are multiple rows already in source table, how to tackle it ?
for example :
with rws as (
select 'row1' ID, 'split_1,into_1,rows_1' str from dual
UNION
select 'row2' ID, 'split_2,into_2,rows_2' str from dual)
select regexp_substr ( str,'[^,]+',1,level) value
from rws
connect by level <= length ( str ) - length ( replace ( str, ',' ) ) + 1;
row1 split_1
row1 into_1
row1 rows_1
row2 rows_2
row2 into_2
row1 rows_1
row2 rows_2
row2 split_2
row1 into_1
row1 rows_1
row2 rows_2
row2 into_2
row1 rows_1
row2 rows_2
Using distinct in selection will not work in my case, my source table has more than 10k records, and number of duplicates created due to use of Connect by is more than 1M.
Is there any other way to split Column in rows for source table that already has many rows ?