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,'[^,]+'))+1order by 1,2
A 表tb1 tb2是输出物表…… 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;
呵呵,问题很有意思,一般是反着问的。 我有1 a 1 b 1 c 如何得到 1 a,b,c
--创建表 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.
(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,'[^,]+'))+1order 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;
我有1 a 1 b 1 c
如何得到 1 a,b,c
--创建表
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.