--创建函数(引用别人的) /************************************************************************************ 创建者:曾浩 创建时间:2007-9-27 最新修改者:曾浩 最新修改时间:2007-9-27 用途:改进的split函数, 实现这样的效果 输入字符串123,123,234,345,234,345,456和字符串, 输出123,234,345,456 ************************************************************************************/ create or replace function ZH_SPLIT(v_string in varchar2, v_delimiter in varchar2) return varchar2 is j int:=0; i int:=1; len_string int:=0; len_delimiter int:=0; str varchar2(4000); v_return varchar2(4000); begin len_string := LENGTH(v_string); len_delimiter := LENGTH(v_delimiter); while j < len_string loop j := INSTR(v_string, v_delimiter, i); if j = 0 then j := len_string; str := SUBSTR(v_string, i); if instr(v_return, str) > 0 then null; else v_return:=v_return||str||','; end if; if i >= len_string then exit; end if; else str := SUBSTR(v_string, i, j - i); i := j + len_delimiter; if instr(v_return, str) > 0 then null; else v_return:=v_return||str||','; end if; end if; end loop; v_return := substr(v_return, 1, length(v_return)-1); return v_return; end;---执行如下sqlSQL> select a1, ZH_SPLIT(replace(ltrim(max(sys_connect_by_path(a2,'|')),'|'),'|' ,','),',') as a2 2 from ( 3 select a1,a2, 4 rank()over(order by a1) + row_number()over(order by a1) RN, 5 row_number()over(partition by a1 order by a1) RM 6 from test1023 7 ) 8 start with RM=1 9 connect by prior RN=RN-1 10 group by 11 a1; A1 A2 ---------- ----------------------------------------------------------------------- 1 ss,ff,gg,jj,nn,pp 2 jj,hh,mm
with a as(select 1 a1,'ss,ff,gg,jj' a2 from dual union all select 1,'ss,nn,jj' from dual union all select 1,'pp' from dual union all select 2,'jj,hh' from dual union all select 2,'mm' from dual)
select a1,wm_concat(distinct a2) from( select a.a1,substr(a.a2,instr(','||a.a2,',',1,b.rn),instr(a.a2||',',',',1,b.rn)-instr(','||a.a2,',',1,b.rn))a2 from a, (select * from (select distinct a1 from a), (select rownum rn from dual connect by rownum<5))b where a.a1=b.a1 and length(a.a2)-length(replace(a.a2,',',''))+1>=b.rn )group by a1A1 WM_CONCAT(DISTINCTA2) 1 ff,gg,jj,nn,pp,ss 2 hh,jj,mm
--创建函数(引用别人的)
/************************************************************************************
创建者:曾浩
创建时间:2007-9-27
最新修改者:曾浩
最新修改时间:2007-9-27
用途:改进的split函数,
实现这样的效果
输入字符串123,123,234,345,234,345,456和字符串,
输出123,234,345,456
************************************************************************************/
create or replace function ZH_SPLIT(v_string in varchar2, v_delimiter in varchar2)
return varchar2
is
j int:=0;
i int:=1;
len_string int:=0;
len_delimiter int:=0;
str varchar2(4000);
v_return varchar2(4000);
begin
len_string := LENGTH(v_string);
len_delimiter := LENGTH(v_delimiter);
while j < len_string
loop
j := INSTR(v_string, v_delimiter, i);
if j = 0 then
j := len_string;
str := SUBSTR(v_string, i);
if instr(v_return, str) > 0 then
null;
else
v_return:=v_return||str||',';
end if;
if i >= len_string then
exit;
end if;
else
str := SUBSTR(v_string, i, j - i);
i := j + len_delimiter;
if instr(v_return, str) > 0 then
null;
else
v_return:=v_return||str||',';
end if;
end if;
end loop;
v_return := substr(v_return, 1, length(v_return)-1);
return v_return;
end;---执行如下sqlSQL> select a1, ZH_SPLIT(replace(ltrim(max(sys_connect_by_path(a2,'|')),'|'),'|'
,','),',') as a2
2 from (
3 select a1,a2,
4 rank()over(order by a1) + row_number()over(order by a1) RN,
5 row_number()over(partition by a1 order by a1) RM
6 from test1023
7 )
8 start with RM=1
9 connect by prior RN=RN-1
10 group by
11 a1; A1 A2
---------- -----------------------------------------------------------------------
1 ss,ff,gg,jj,nn,pp
2 jj,hh,mm
union all select 1,'ss,nn,jj' from dual
union all select 1,'pp' from dual
union all select 2,'jj,hh' from dual
union all select 2,'mm' from dual)
select a1,wm_concat(distinct a2) from(
select a.a1,substr(a.a2,instr(','||a.a2,',',1,b.rn),instr(a.a2||',',',',1,b.rn)-instr(','||a.a2,',',1,b.rn))a2
from a,
(select * from
(select distinct a1 from a),
(select rownum rn from dual connect by rownum<5))b
where a.a1=b.a1
and length(a.a2)-length(replace(a.a2,',',''))+1>=b.rn
)group by a1A1 WM_CONCAT(DISTINCTA2)
1 ff,gg,jj,nn,pp,ss
2 hh,jj,mm