with T as ( select 12343 caller, '11A' siginid, '46-1;8-1;9-1' queue_list from dual union select 1342, '21B', '52-1;52-3' from dual) select caller,queue_list,r.rn,regexp_substr(queue_list, '[^;]+', 1, r.rn) from t, (select rownum rn from dual connect by level < 100) r where regexp_substr(queue_list, '[^;]+', 1, r.rn) is not null order by caller,r.rn;
with b as (select '46-1;8-1;9-1' a from dual union all select '52-1;52-3;52-6' from dual ) select a from (select substr(a,1,instr(a,';')-1) a, substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b ,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c from b) union all select b from (select substr(a,1,instr(a,';')-1) a, substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b ,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c from b) union all select c from (select substr(a,1,instr(a,';')-1) a, substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b ,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c from b) --result:46-1 52-1 8-1 52-3 9-1 52-6
--modify with b as (select '46-1;8-1;9-1' a from dual union all select '52-1;52-3' from dual union all select '52-1;52-3;54-3' from dual )select * from ( select a from (select substr(a,1,instr(a,';')-1) a, substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b ,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c from (select case when length(a)-length(replace(a,';'))<2 then a||';' else a end a from b) ) union all select b from (select substr(a,1,instr(a,';')-1) a, substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b ,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c from (select case when length(a)-length(replace(a,';'))<2 then a||';' else a end a from b) ) union all select c from (select substr(a,1,instr(a,';')-1) a, substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b ,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c from (select case when length(a)-length(replace(a,';'))<2 then a||';' else a end a from b) )) where a is not null--最多只能有两个; --对一个;的处理是末尾增加; --修改为空的判断 --没有10G的函数来得快捷--结果: 1 46-1 2 52-1 3 52-1 4 8-1 5 52-3 6 52-3 7 9-1 8 54-3
with tbl as ( select '123' as caller, 'UserAD' as siginid, '46-1;8-1;9-1' as queue_list from dual union all select '456' as caller, 'UserBE' as siginid, '52-1;52-3' as queue_list from dual ) select caller, siginid, regexp_substr(queue_list, '[^;]+', 1, line) as queue from tbl a, (select rownum as line from dual connect by rownum <= (select max(length(queue_list) - length(replace(queue_list, ';', ''))) + 1 from tbl)) where regexp_substr(queue_list, '[^;]+', 1, line) is not null order by caller, siginid, line; CALLER SIGINID QUEUE ------ ------- ------------------------------------------------ 123 UserAD 46-1 123 UserAD 8-1 123 UserAD 9-1 456 UserBE 52-1 456 UserBE 52-3
有了查询语句,创建视图还不简单?create or replace view v_name as 查询语句
楼主你不看别人给你的回贴吗?SQL都给你了,按照SQL建视图就可以了。
with tbl as ( select '123' as caller, 'UserAD' as siginid, '46-1;8-1;9-1' as queue_list from dual union all select '456' as caller, 'UserBE' as siginid, '52-1;52-3' as queue_list from dual ) select caller, siginid, regexp_substr(queue_list||';', '[^;]+', 1, level) as queue from tbl a connect by caller= prior caller and prior dbms_random.value is not null and level <= length(regexp_replace(queue_list||';', '[^;]+',null))
with T as (
select 12343 caller, '11A' siginid, '46-1;8-1;9-1' queue_list from dual union
select 1342, '21B', '52-1;52-3' from dual)
select caller,queue_list,r.rn,regexp_substr(queue_list, '[^;]+', 1, r.rn)
from t,
(select rownum rn from dual connect by level < 100) r
where regexp_substr(queue_list, '[^;]+', 1, r.rn) is not null
order by caller,r.rn;
as
(select '46-1;8-1;9-1' a from dual
union all
select '52-1;52-3;52-6' from dual
)
select a from
(select substr(a,1,instr(a,';')-1) a,
substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b
,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c
from b)
union all
select b from
(select substr(a,1,instr(a,';')-1) a,
substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b
,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c
from b)
union all
select c from
(select substr(a,1,instr(a,';')-1) a,
substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b
,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c
from b)
--result:46-1
52-1
8-1
52-3
9-1
52-6
with b
as
(select '46-1;8-1;9-1' a from dual
union all
select '52-1;52-3' from dual
union all
select '52-1;52-3;54-3' from dual
)select * from
(
select a from
(select substr(a,1,instr(a,';')-1) a,
substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b
,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c
from
(select case when length(a)-length(replace(a,';'))<2 then a||';' else a end a from b)
)
union all
select b from
(select substr(a,1,instr(a,';')-1) a,
substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b
,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c
from
(select case when length(a)-length(replace(a,';'))<2 then a||';' else a end a from b)
)
union all
select c from
(select substr(a,1,instr(a,';')-1) a,
substr(a,instr(a,';')+1,instr(a,';',1,2)-instr(a,';')-1) b
,substr(a,instr(a,';',1,2)+1,instr(a,';',-1,1)) c
from
(select case when length(a)-length(replace(a,';'))<2 then a||';' else a end a from b)
))
where a is not null--最多只能有两个;
--对一个;的处理是末尾增加;
--修改为空的判断
--没有10G的函数来得快捷--结果:
1 46-1
2 52-1
3 52-1
4 8-1
5 52-3
6 52-3
7 9-1
8 54-3
with tbl as
(
select '123' as caller, 'UserAD' as siginid, '46-1;8-1;9-1' as queue_list from dual
union all
select '456' as caller, 'UserBE' as siginid, '52-1;52-3' as queue_list from dual
)
select caller, siginid, regexp_substr(queue_list, '[^;]+', 1, line) as queue
from tbl a,
(select rownum as line from dual
connect by rownum <= (select max(length(queue_list) - length(replace(queue_list, ';', ''))) + 1 from tbl))
where regexp_substr(queue_list, '[^;]+', 1, line) is not null
order by caller, siginid, line;
CALLER SIGINID QUEUE
------ ------- ------------------------------------------------
123 UserAD 46-1
123 UserAD 8-1
123 UserAD 9-1
456 UserBE 52-1
456 UserBE 52-3
有了查询语句,创建视图还不简单?create or replace view v_name as 查询语句
楼主你不看别人给你的回贴吗?SQL都给你了,按照SQL建视图就可以了。
with tbl as
(
select '123' as caller, 'UserAD' as siginid, '46-1;8-1;9-1' as queue_list from dual
union all
select '456' as caller, 'UserBE' as siginid, '52-1;52-3' as queue_list from dual
)
select caller, siginid, regexp_substr(queue_list||';', '[^;]+', 1, level) as queue
from tbl a
connect by caller= prior caller
and prior dbms_random.value is not null
and level <= length(regexp_replace(queue_list||';', '[^;]+',null))