-------strName 目标字符串
------subName 分离字符串
------len 分离字符的下标
create or replace function subString(strName in varchar2, subName in varchar, len in number) return varchar2 is
n number(10);
p number(10);
retunstr varchar2(20);
maxSize number(10);
outMaxize exception;
begin
n:=0;
maxSize:=getstrnum(strName,subName);
if(len>maxSize) then
raise outMaxize;
end if;
if (len=1) then
p:=0;
else
p:=instr(strName,subName,1,len-1);
end if;
n:=instr(strName,subName,1,len);
retunstr:=substr(strName,p+1,n-p-1);
return(retunstr);
exception
when outMaxize then
return('-1');
end ;
--------- str 分离字符串
-------- s 下标
--------此方法目的是获取分离字符串在字符串中的个数,以此来获取分离字符的下标
create or replace function getStrNum (str in varchar2,s in varchar2) return number is
len number(10);
num number(10);
begin
len:=length(str);
num:=0;
for i in 1..len loop
if (substr(str,i,1)=s) then
num:=num+1;
end if;
end loop;
return(num);
end ;