with a as
(select 1 as col1, 'a,d' as col2 from dual
union all
select 2 as col1, 'a,aad,sd' as col2 from dual)
select distinct sub.col1, sub.col2 from
(select col1, regexp_substr(col2, '\w+', 1, level) col2 from a
connect by level < 10) sub
where sub.col2 is not null
order by sub.col1;
with t as
(
select 1 col1,'a,d' col2 from dual
union all
select 2 col1,'a,aad,sd' col2 from dual
)
select distinct t.col1,regexp_substr(t.col2,'[^,]+',1,level)
from t
connect by level<=length(regexp_replace(t.col2,'[^,]+'))+1
order by 1,2
create table tb1
(col1 varchar2(4),
col2 varchar2(50)
)
create table tb2
(col1 varchar2(4),
col2 varchar2(50)
)
create or replace procedure p_insert is
cursor cur_tb1 is
select * from tb1;
row_cur_tb1 cur_tb1%rowtype;
begin
execute immediate 'truncate table tb2';
for row_cur_tb1 in cur_tb1
loop
insert into tb2
select row_cur_tb1.col1,
regexp_substr(row_cur_tb1.col2, '[^,]+', 1, rownum)
from dual
connect by rownum <= length(regexp_replace(row_cur_tb1.col2, '[^,]+')) + 1;
end loop;
commit;
exception
when others then
dbms_output.put_line('error');
rollback;
end p_insert;
--创建表
create table tb1
(col1 varchar2(4),
col2 varchar2(50)
);
--插入数据
insert into tb1
select '1','a,d' from dual
union all
select '2','a,aad,sd' from dual;
Commit;
--执行查询
select t1.col1,substr(t1.col2, t2.id, instr(t1.col2||',',',',t2.id)-t2.id) col2
from tb1 t1,
(select rownum id from dual connect by rownum<(select max(length(col2)) from tb1)) t2
where t2.id<=length(t1.col2)
and instr(','||t1.col2,',',t2.id)=t2.id
order by t1.col1,t2.id;
--删除表
drop table tb1 cascade constraints;
COL1 COL2
---- --------------------------------------------------
1 a
1 d
2 a
2 aad
2 sd
5 rows selected.
