统计字符串出现的次数

昨天学习instr函数(http://blog.youkuaiyun.com/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