oracle将字符串转成数组_oracle根据分隔符将字符串分割成数组函数

create or replace type mytype as table of number;

--如果定义成varchar

--CREATE OR REPLACE type mytype as table of varchar2(4000);

-- 将字符串分割成数组

function my_split(piv_str in varchar2, piv_delimiter in varchar2)

--piv_str 为字符串&＃xff0c;piv_delimiter 为分隔符

return mytype is

j int :&＃61; 0;

i int :&＃61; 1;

len int :&＃61; 0;

len1 int :&＃61; 0;

str varchar2(4000);

my_split mytype :&＃61; mytype();

begin

len :&＃61; length(piv_str);

len1 :&＃61; length(piv_delimiter);

while j

j :&＃61; instr(piv_str, piv_delimiter, i);

if j &＃61; 0 then

j :&＃61; len;

str :&＃61; substr(piv_str, i);

my_split.extend;

my_split(my_split.count) :&＃61; str;

if i >&＃61; len then

exit;

end if;

else

str :&＃61; substr(piv_str, i, j - i);

i :&＃61; j &＃43; len1;

my_split.extend;

my_split(my_split.count) :&＃61; str;

end if;

end loop;

return my_split;

end my_split;

-- 函数调用,两个参数:字符串和分隔符

select column_value from table(my_split(&＃39;7369,7499,7521,7844&＃39;,&＃39;,&＃39;));

-- 输出结果如下

7369

7499

7521

7844

分享到&＃xff1a;

2013-01-16 10:29

浏览 8892

分类:数据库

评论