Using Oracle REGEXP_SUBSTR function & CONNECT BY LEVEL clause to Decompose String－DanBrother的部落格

Using Oracle REGEXP_SUBSTR function & CONNECT BY LEVEL clause to Decompose String

col String format a10
select REGEXP_SUBSTR('123,456,789','[^,]+',1,LEVEL) as String from dual
connect by REGEXP_SUBSTR('123,456,789','[^,]+',1,LEVEL) is not null;
>>
STRING
----------
123
456
789

set serveroutput on size 1000000
declare
type name_list_type is table of varchar2(20)
index by pls_integer;
name_list name_list_type;
v_name_string varchar2(50);
begin
v_name_string :='John,Bob,Cathy,Don,Enoch';
select REGEXP_SUBSTR(v_name_string ,'[^,]+',1,LEVEL) bulk collect into name_list
from dual connect by REGEXP_SUBSTR(v_name_string ,'[^,]+',1,LEVEL) is not null;

for i in name_list.FIRST .. name_list.LAST loop
dbms_output.put_line('Name('||i||') : '||name_list(i));
end loop;
end;
/

Name(1) : John
Name(2) : Bob
Name(3) : Cathy
Name(4) : Don
Name(5) : Enoch

--------------------------------------------------------------------------------------------------------------------------------------

The follow example demonstrates a way to decompose comma-seperated string with multi rows:

create table decompose_test
(id number,
list_values varchar2(30)
);

insert into decompose_test(id,list_values)
values(1,'10,20,30');

insert into decompose_test(id,list_values)
values(2,'100,200,300');

insert into decompose_test(id,list_values)
values(3,'1000,2000,3000');

commit;

select * from decompose_test;
>>
SQL> select * from decompose_test;

ID LIST_VALUES
---------- ------------------------------
1 10,20,30
2 100,200,300
3 1000,2000,3000

with temp as (
select id,list_values
from decompose_test
order by id
)
SELECT distinct id, REGEXP_SUBSTR(list_values, '[^,]+', 1, level) value
FROM temp
CONNECT BY REGEXP_SUBSTR(list_values,'[^,]+',1,level) is not null
order by id;

>>
ID VALUE
---------- ---------
1 10
1 20
1 30
2 100
2 200
2 300
3 1000
3 2000
3 3000

9 rows selected