判断A字符串在B字符串中连续出现的最大次数

create or replace function FUN_INSTR_CNT(in_str   varchar2,
                                         in_instr varchar2) return number as
  i      number := 1;
  v_lsx  varchar2(4000) default '';
  vv_lsx varchar2(4000) default '';
  v_len  number := 1;
begin
  if length(in_instr) >= 1 then
    v_len := length(in_str);
    while i <= v_len loop
      v_lsx := v_lsx || in_instr;
      i     := i + 1;
    end loop;
  
    i := 1;
    while i <= v_len loop
      vv_lsx := substr(v_lsx, 1, v_len - i + 1);
    
      if instr(in_str, vv_lsx) >= 1 and length(vv_lsx) >= length(in_instr) then
        return length(vv_lsx) / length(in_instr);
      end if;
      i := i + 1;
    end loop;
  end if;
  return 0;
end;
/

执行结果

 
SQL> select FUN_INSTR_CNT('aafffbbfffffd','f'),FUN_INSTR_CNT('0000','1'),FUN_INSTR_CNT('101010','11'),FUN_INSTR_CNT('101010','1'),FUN_INSTR_CNT('101010','1'),FUN_INSTR_CNT('10101011','1') from dual;
;
 
FUN_INSTR_CNT('AAFFFBBFFFFFD', FUN_INSTR_CNT('0000','1') FUN_INSTR_CNT('101010','11') FUN_INSTR_CNT('101010','1') FUN_INSTR_CNT('101010','1') FUN_INSTR_CNT('10101011','1')
------------------------------ ------------------------- ---------------------------- --------------------------- --------------------------- -----------------------------
                             5                         0                            0                           1                           1                             2
 
SQL> 
SQL>