fun_get_split_str 取得一個字串,按照特定字元分隔的,特定位置的值.

create or replace function fun_get_split_str(p_str varchar2,
p_delimiter varchar2 default ',',
p_pos integer)
return varchar2
/*
auther : jidongzheng
desc :取得一個字串,按照特定字元分隔的值.
para:
p_str :輸入,提取的源字串.
p_delimiter:分隔符
p_pos :提取的目標字元位置.從1開始

*/

as
j INT := 0;
i INT := 1;
k int := 0;
len INT := 0;
len1 INT := 0;
str VARCHAR2(4000);

begin

len := LENGTH(p_str);
len1 := LENGTH(p_delimiter);

WHILE j < len LOOP

j := INSTR(p_str, p_delimiter, i);

IF j = 0 THEN
j := len;
str := SUBSTR(p_str, i);
k := k + 1;
IF i >= len THEN
EXIT;
END IF;
ELSE
str := SUBSTR(p_str, i, j - i);
i := j + len1;
k := k + 1;
END IF;
if k = p_pos then
exit;
end if;

end loop;

if k = p_pos then
return str;
else
return null;
end if;

end;

[@more@]