oracle根据分隔符将字符串分割成数组函数
--创建表类型 www.2cto.com
Sql代码
create or replace type mytype as table of number;
--如果定义成varchar
--CREATE OR REPLACE type mytype as table of varchar2(4000);
-- 将字符串分割成数组 www.2cto.com
Sql代码
function my_split(piv_str in varchar2, piv_delimiter in varchar2)
--piv_str 为字符串，piv_delimiter 为分隔符
return mytype is
j int := 0;
i int := 1;
len int := 0;
len1 int := 0;
str varchar2(4000);
my_split mytype := mytype();
begin
len := length(piv_str);
len1 := length(piv_delimiter);
while j < len loop
j := instr(piv_str, piv_delimiter, i);
if j = 0 then
j := len;
str := substr(piv_str, i);
my_split.extend;
my_split(my_split.count) := str;
if i >= len then
exit;
end if;
else
str := substr(piv_str, i, j - i);
i := j + len1;
my_split.extend;
my_split(my_split.count) := str;
end if;
end loop;
return my_split;
end my_split;
-- 函数调用,两个参数:字符串和分隔符
Sql代码
select column_value from table(my_split('7369,7499,7521,7844',','));
-- 输出结果如下
7369
7499
7521
7844