oracle分割字符串，返回结果集--分割字符串，返回结果集
function split(v_str in varchar2,v_separator in varchar2:=',') return tab_str1 pipelined is
v_idx pls_integer;
v_list varchar2(8000):= v_str;
begin
loop
v_idx := instr(v_list,v_separator);
if v_idx > 0 then
pipe row(obj_str1(substr(v_list,1,v_idx-1)));
v_list := substr(v_list,v_idx+length(v_separator));
else
pipe row(obj_str1(v_list));
exit;
end if;
end loop;
return;
end split;
--分割字符串，返回结果集
function split_2(v_str in varchar2,v_separator1 in varchar2:=',',v_separator2 in varchar2:='|') return tab_str2 pipelined is
v_idx1 pls_integer;
v_idx2 pls_integer;
v_list1 varchar2(8000):= v_str;
v_list2 varchar2(8000);
v_str1 varchar2(8000);
v_str2 varchar2(8000);
begin
loop
v_idx1 := instr(v_list1,v_separator1);
if v_idx1 > 0 then
v_list2 := substr(v_list1,1,v_idx1-1);
v_idx2 := instr(v_list2,v_separator2);
if v_idx2 > 0 then
v_str1 :=substr(v_list2,1,v_idx2-1);
v_str2 :=substr(v_list2,v_idx2+length(v_separator2));
else
v_str1 :=v_list2;
v_str2 :=null;
end if;
pipe row(obj_str2(v_str1,v_str2));
v_list1 := substr(v_list1,v_idx1+length(v_separator1));
else
v_idx2 := instr(v_list1,v_separator2);
if v_idx2 > 0 then
v_str1 :=substr(v_list1,1,v_idx2-1);
v_str2 :=substr(v_list1,v_idx2+length(v_separator2));
else
v_str1 :=v_list1;
v_str2 :=null;
end if;
pipe row(obj_str2(v_str1,v_str2));
exit;
end if;
end loop;
return;
end split_2;
-
------------------------------------------------------------------------------------------
--分割字符串，返回结果集
function split_big(v_str clob,v_separator in varchar2:=',') return tab_str1 pipelined is
v_idx pls_integer;
v_list clob:= v_str;
begin
loop
v_idx := instr(v_list,v_separator);
if v_idx > 0 then
pipe row(obj_str1(substr(v_list,1,v_idx-1)));
v_list := substr(v_list,v_idx+length(v_separator));
pipe row(obj_str1(v_list));
exit;
end if;
end loop;
return;
end split_big;
--分割字符串，返回结果集
function split_big_2(v_str clob,v_separator1 in varchar2:=',',v_separator2 in varchar2:='|') return tab_str2 pipelined is v_idx1 pls_integer;
v_idx2 pls_integer;
v_list1 clob:= v_str;
v_list2 clob;
v_str1 varchar2(8000);
v_str2 varchar2(8000);
begin
loop
v_idx1 := instr(v_list1,v_separator1);
if v_idx1 > 0 then
v_list2 := substr(v_list1,1,v_idx1-1);
v_idx2 := instr(v_list2,v_separator2);
if v_idx2 > 0 then
v_str1 :=substr(v_list2,1,v_idx2-1);
v_str2 :=substr(v_list2,v_idx2+length(v_separator2));
else
v_str1 :=v_list2;
v_str2 :=null;
end if;
pipe row(obj_str2(v_str1,v_str2));
v_list1 := substr(v_list1,v_idx1+length(v_separator1));
else
v_idx2 := instr(v_list1,v_separator2);
if v_idx2 > 0 then
v_str1 :=substr(v_list1,1,v_idx2-1);
v_str2 :=substr(v_list1,v_idx2+length(v_separator2));
else
v_str1 :=v_list1;
v_str2 :=null;
end if;
pipe row(obj_str2(v_str1,v_str2));
exit;
end if;
end loop;
return;
end split_big_2;
-------------------------------------------------------------------------------------------
-
-分割字符串，并取出指定位置的字符串
function splitstr(v_str in varchar2,v_splitstr in varchar2,v_index in number) return varchar2
is
v_j int := 0;
v_i int := 1;
v_len int := 0;
v_len1 int := 0;
v_str1 varchar2(4000);
v_cnt int:=1;
begin
v_len:= length(v_str);
v_len1:= length(v_splitstr);
while v_j < v_len loop
v_j:= instr(v_str, v_splitstr, v_i);
if v_j = 0 then
v_j := v_len;
v_str1:= substr(v_str, v_i);
if v_index=v_cnt then
return v_str1;
end if;
v_cnt:=v_cnt+1;
if v_i >= v_len then
end if;
else
v_str1 := substr(v_str, v_i, v_j - v_i);
v_i:= v_j + v_len1;
if v_index=v_cnt then
return v_str1;
end if;
v_cnt:=v_cnt+1;
end if;
end loop;
return null;
end splitstr;
--测试
--col1 是系统固定列名
oracle 字符串转数组
l1 from table(PKG_PUBLIC.SPLIT('123,213,113',',')) ol --结果：
123
213
113

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。