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 為字元串，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;

-- 函數調用,兩個參數:字元串和分隔符

select column_value from table(my_split('7369,7499,7521,7844',','));

-- 輸出結果如下

7369

7499

7521

7844

分享到：

2013-01-16 10:29

浏覽 8892

分類:資料庫

評論