字符串分割（转载）

create or replace procedure pro_splitstr(iStart in NUMBER,
sPattern in VARCHAR2, sBuffer in VARCHAR2,
sResult out VARCHAR2, iNextPos out NUMBER) is
nPos1 number;
nPos2 number;
begin
nPos1 := Instr(sBuffer, sPattern, iStart);
if nPos1 = 0 then
sResult := NULL;
else
nPos2 := Instr (sBuffer, sPattern, nPos1 + 1);
if nPos2 = 0 then
sResult := Rtrim(Ltrim(Substr(sBuffer, nPos1 + 1)));
iNextPos := nPos2;
else
sResult := Substr(sBuffer, nPos1 + 1, nPos2 - nPos1 - 1);
iNextPos := nPos2;
end if;
end if;
end pro_splitstr;
---字符串分割