昨天学习instr函数(http://blog.csdn.net/BOBO12082119/archive/2011/03/14/6248848.aspx ),当我写子串在母串中出现的位置的查询时,有个想法:能不能找出子串在母串中出现的次数,或者oracle有没有这样的统计函数呢?由于才学oracle没多久,所以很多知识还未触及,所以想了个最笨的方法,如下:
SQL> select sum(case when instr('chen_libo_bobo12082119','bo')>0 then 1 else 0 end) from dual;
SUM(CASEWHENINSTR('CHEN_LIBO_B
------------------------------
1
如果正确的统计的话,应该是3次。很明显我写的语句不能正确的统计出来。
找了找,有下面两种方法:
方法1:自建一个函数
create or replace function InStrTimes(main_str in varchar2,sub_str in varchar2) return number
as
v_main_str varchar2(4000);
v_ms_len number;
v_ss_len number;
v_Pos number:= 0;
v_PosR number:= 0;
v_Count number:= 0;
v_Ret number:=0;
begin
v_main_str := main_str;
v_ss_len := length(sub_str);
v_ms_len := length(main_str);
begin
while v_ms_len >= v_ss_len loop
v_Pos := instr(v_main_str,sub_str) ;
if v_Pos =0 then
exit;
else
v_Ret:=v_Ret+1;
v_main_str := substr(v_main_str,v_Pos+v_ss_len);
v_ms_len := length(v_main_str);
v_Count := v_Count +1;
v_PosR :=v_PosR+v_Pos-1;
end if;
end loop;
exception
when others then
raise;
end;
return v_Ret;
end;
SQL> select InStrTimes('chen_linbo_bobo12082119','bo') from dual;
INSTRTIMES('CHEN_LINBO_BOBO120
------------------------------
3
SQL> select InStrTimes('chen_linbo_bobo12082119','_') from dual;
INSTRTIMES('CHEN_LINBO_BOBO120
------------------------------
2
方法2:用几个oracle的内置函数
select length(regexp_replace(replace('chen_linbo_bobo12082119','_','@'),'[^@]+','')) count from dual;
COUNT
----------
2
select length(regexp_replace(replace('chen_linbo_bobo12082119','1','@'),'[^@]+','')) count from dual;
COUNT
----------
3