自己写一个把,google很多的
[php]
SQL> create or replace function InStrTimes(main_str in varchar2,sub_str in varchar2) return number
2 as
3 v_main_str varchar2(4000);
4 v_ms_len number;
5 v_ss_len number;
6 v_Pos number:= 0;
7 v_PosR number:= 0;
8 v_Count number:= 0;
9 v_Ret number:=0;
10 begin
11 v_main_str := main_str;
12 v_ss_len := length(sub_str);
13 v_ms_len := length(main_str);
14 begin
15 while v_ms_len >= v_ss_len loop
16 v_Pos := instr(v_main_str,sub_str) ;
17 if v_Pos =0 then
18 exit;
19 else
20 v_Ret:=v_Ret+1;
21 v_main_str := substr(v_main_str,v_Pos+v_ss_len);
22 v_ms_len := length(v_main_str);
23 v_Count := v_Count +1;
24 v_PosR :=v_PosR+v_Pos-1;
25 end if;
26 end loop;
27 exception
28 when others then
29 raise;
30 end;
31 return v_Ret;
32 end;
33 /
Function created
SQL>
SQL> select InStrTimes('ZRPzrpABCzrpabc','zrp') from dual;
INSTRTIMES('ZRPZRPABCZRPABC','
------------------------------
2
SQL> select InStrTimes('AABBCCAABBCC','BB') from dual;
INSTRTIMES('AABBCCAABBCC','BB'
------------------------------
2
SQL> select InStrTimes('TESTString','find') from dual;
INSTRTIMES('TESTSTRING','FIND'
------------------------------
0
SQL>
[/php]