....
If the str values were separated by commas I think the following would work....
no, it don't work.
rajesh@ORA10G> set feedback off
rajesh@ORA10G> drop table t purge;
rajesh@ORA10G> create table t(id int,str varchar2(10));
rajesh@ORA10G> insert into t values(1,'A,B,C');
rajesh@ORA10G> commit;
rajesh@ORA10G> set feedback on
rajesh@ORA10G> select * from T;
ID STR
---------- ----------
1 A,B,C
1 row selected.
rajesh@ORA10G> SELECT
2 id,
3 trim(regexp_substr(str, '[^,]+', 1, LEVEL)) as id
4 FROM t
5 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 ;
ID ID
---------- ----------
1 A
1 B
1 C
3 rows selected.
with single row in Table 'T' this works, but when multiple rows exists, this breaks.
rajesh@ORA10G> insert into t values(2,'D,E');
1 row created.
rajesh@ORA10G> SELECT
2 id,
3 trim(regexp_substr(str, '[^,]+', 1, LEVEL)) as id
4 FROM t
5 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 ;
ID ID
---------- ----------
1 A
1 B
1 C
2 E
1 C
2 D
1 B
1 C
2 E
1 C
10 rows selected.
so the right solution would be this (Table un-nesting)
rajesh@ORA10G> select id,
2 regexp_substr(str,'[^,]+',1,column_value) as x
3 from t,
4 TABLE(cast(multiset(select level
5 from dual
6 connect by level <= length(str) -
7 length(replace(str,','))+1)
8 as sys.odcinumberlist))
9 /
ID X
---------- ----------
1 A
1 B
1 C
2 D
2 E
5 rows selected.
rajesh@ORA10G>
Try to stick with 'substr' and 'instr' approach rather than Regexp, "Regexp neat, but Huge CPU cost"
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:73830657104020#2325312300346613472