Code:
SQL>
SQL> --
SQL> select * from t;
X Y
---------- ----------------------------------------
1 NY,NJ,CA
2 US,UK
3 AS,EU,NA
4 AAA,BBBB,C,DDDDD,EE,F,GGGGGG
5
6 XYZ
6 rows selected.
SQL>
SQL> -- Using SUBSTR, INSTR functions.
SQL> select x,
2 case when iter.pos = 1 and length(y)-length(replace(y,','))+1 = 1 then y
3 when iter.pos = 1 then substr(y,1,instr(y,',',1,iter.pos)-1)
4 when iter.pos = length(y)-length(replace(y,','))+1 then substr(y,instr(y,',',1,iter.pos-1)+1)
5 else substr(y, instr(y,',',1,iter.pos-1)+1, instr(y,',',1,iter.pos) - instr(y,',',1,iter.pos-1) - 1)
6 end as token
7 from t,
8 ( select level as pos
9 from dual
10 connect by level <= (select max(length(y)-length(replace(y,','))+1) from t)
11 ) iter
12 where iter.pos <= nvl(length(y)-length(replace(y,','))+1,1)
13 order by x, pos
14 ;
X TOKEN
---------- ----------------------------------------
1 NY
1 NJ
1 CA
2 US
2 UK
3 AS
3 EU
3 NA
4 AAA
4 BBBB
4 C
4 DDDDD
4 EE
4 F
4 GGGGGG
5
6 XYZ
17 rows selected.
SQL>
SQL> -- Same query in stages.
SQL> with iter(pos) as (
2 select level as pos
3 from dual
4 connect by level <= (select max(length(y) - length(replace(y,',')) + 1) from t)
5 ),
6 data(x, y, token_count) as (
7 select x, y, length(y) - length(replace(y, ',')) + 1 as token_count
8 from t
9 ),
10 combined as (
11 select d.x, d.y, iter.pos, d.token_count,
12 case when iter.pos > 1 then instr(y, ',', 1, iter.pos-1)
13 end as prev_indx,
14 instr(y, ',', 1, iter.pos) as indx
15 from data d, iter
16 where iter.pos <= nvl(d.token_count, 1)
17 )
18 select x,
19 case when pos = 1 and token_count = 1 then y
20 when pos = 1 then substr(y, 1, indx - 1)
21 when pos = token_count then substr(y, prev_indx + 1)
22 else substr(y, prev_indx + 1, indx - prev_indx - 1)
23 end as token
24 from combined
25 order by x, pos
26 ;
X TOKEN
---------- ----------------------------------------
1 NY
1 NJ
1 CA
2 US
2 UK
3 AS
3 EU
3 NA
4 AAA
4 BBBB
4 C
4 DDDDD
4 EE
4 F
4 GGGGGG
5
6 XYZ
17 rows selected.
SQL>
SQL>
SQL> -- Another one using regular expressions.
SQL> -- regexp_count is in version 11g Release 1 and higher
SQL> select x,
2 regexp_substr(y,'[^,]+',1,iter.pos) as token
3 from t,
4 ( select level as pos
5 from dual
6 connect by level <= (select max(regexp_count(y,',')+1) from t)
7 ) iter
8 where iter.pos <= nvl(regexp_count(y,',')+1,1)
9 order by x, pos
10 ;
X TOKEN
---------- ----------------------------------------
1 NY
1 NJ
1 CA
2 US
2 UK
3 AS
3 EU
3 NA
4 AAA
4 BBBB
4 C
4 DDDDD
4 EE
4 F
4 GGGGGG
5
6 XYZ
17 rows selected.
SQL>
SQL>