create or replace function fun(s in varchar2,s1 in varchar2) return number is i number := 1; begin while instr(s,s1,1,i) > 0 loop i := i + 1; end loop; return i; end fun; /
create or replace function fun(s in varchar2,s1 in varchar2) return number is i number := 0; begin while instr(s,s1,1,i+1) > 0 loop i := i + 1; end loop; return i; end fun; / 有点错误,修改下。
easy select mid, cn from (select mid, count(*) cn from (select substr(s, rownum, 1) mid from (select '6+4+8+2' s from dual) connect by rownum <= length(s)) group by mid) where mid = '+';
return number
is
i number := 1;
begin
while instr(s,s1,1,i) > 0 loop
i := i + 1;
end loop;
return i;
end fun;
/
INSTR('CORPORATE FLOOR','OR', 3, 2)中,源字符串为'CORPORATE FLOOR', 目标字符串为'OR',起始位置为3,取第2个匹配项的位置。
return number
is
i number := 0;
begin
while instr(s,s1,1,i+1) > 0 loop
i := i + 1;
end loop;
return i;
end fun;
/
有点错误,修改下。
select mid, cn
from (select mid, count(*) cn
from (select substr(s, rownum, 1) mid
from (select '6+4+8+2' s from dual)
connect by rownum <= length(s))
group by mid)
where mid = '+';