第一種 直接返回切分的字符串

create or replace function Get_StrArrayLength

(

av_str varchar2,--要分割的字符串

av_split varchar2 --分隔符號

)

return number

is

lv_str varchar2(1000);

lv_length number;

begin

lv_str:=ltrim(rtrim(av_str));

lv_length:=0;

while instr(lv_str,av_split)<>0 loop

lv_length:=lv_length+1;

lv_str:=substr(lv_str,instr(lv_str,av_split)+length(av_split),length(lv_str));

end loop;

lv_length:=lv_length+1;

return lv_length;

end Get_StrArrayLength;第二種 以表的方式返回

create or replace function Get_StrArrayStrOfIndex

(

av_str varchar2,--要分割的字符串

av_split varchar2,--分隔符號

av_index number --取第幾個元素

)

return varchar2

is

lv_str varchar2(1024);

lv_strOfIndex varchar2(1024);

lv_length number;

begin

lv_str:=ltrim(rtrim(av_str));

lv_str:=concat(lv_str,av_split);

lv_length:=av_index;

if lv_length=0 then

lv_strOfIndex:=substr(lv_str,1,av_split)-length(av_split));

else

lv_length:=av_index+1;

lv_strOfIndex:=substr(lv_str,av_split,av_index)+length(av_split),lv_length)-instr(lv_str,av_index)-length(av_split));

end if;

return lv_strOfIndex;

end Get_StrArrayStrOfIndex;