使用方式:
如有字符串: abc||cde||kkkkk
要实现以上字符串的按照“||”来分割,并取出其中第二个字段的内容,则有:
select w_func_getstrpart('abc||cde||kkkk','||',2) from dual;
下面是函数的具体实现方式:
create or replace function w_func_getstrpart(src varchar2, findstr varchar2 default ' ', nthpart integer default 1)
return varchar2 is
res varchar2(500) := findstr;
frompos integer := 1;
partlen integer := 0;
srcstr varchar2(501) := src||findstr;
begin
if nthpart = 1 then frompos := 1; partlen := instr(srcstr, findstr, 1, nthpart) - 1;
elsif nthpart < 1 then frompos := 1; partlen := 0;--length(srcstr);
elsif nthpart > 1 then
frompos := instr(srcstr, findstr, 1, nthpart - 1) + length(findstr);
partlen := instr(srcstr, findstr, 1, nthpart) - instr(srcstr, findstr, 1, nthpart - 1) - length(findstr);
end if;
res := substr(srcstr, frompos, partlen);
return res;
end w_func_getstrpart;
/