(14)自己写一个函数/************************************************************************************创建者:曾浩创建时间: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;