2-1-10 文字列中の指定文字列の数

SQLパズル

ValTable
Val
---------
abbabbaab
ababaaaa
ab
a

文字列Val内の'ab'の数を取得する。

出力結果
Val        文字列abの数
---------  ------------
abbabbaab  3
ababaaaa   2
ab         1
a          0

データ作成スクリプト

create table ValTable(Val) as
select 'abbabbaab' from dual union
select 'ababaaaa' from dual union
select 'ab' from dual union
select 'a'  from dual;

SQL

--■■■Length関数を使う方法■■■
select val,
(Length(val)-nvl(Length(replace(val,'ab')),0))/Length('ab') as "文字列abの数"
from ValTable
order by val desc;

--■■■model句で再帰的にinstr関数かRegExp_Instr関数を使う方法1■■■
select Val,cnt as "文字列abの数"
  from ValTable
 model
 partition by (RowNum as PID)
 dimension by (0 as soeji)
 measures(Val,0 as hitPos,0 as cnt,1 as startPos)
 rules iterate (100) (hitPos[0] = instr(Val[0],'ab',startPos[0]),
                      cnt[0] = case when hitPos[0] = 0
                                    then cnt[0]
                                    else cnt[0]+1 end,
                      startPos[0] = case when hitPos[0] = 0
                                    then startPos[0]
                                    else hitPos[0]+1 end)
order by Val desc;

--■■■model句で再帰的にinstr関数かRegExp_Instr関数を使う方法2■■■
select Val,hitCnt as "文字列abの数"
  from ValTable
 model
 partition by (RowNum as PID)
 dimension by (0 as soeji)
 measures(Val,0 as hitCnt)
 rules iterate (100)
 (hitCnt[0] = case when instr(Val[0],'ab',1,iteration_number+1) = 0
                   then hitCnt[0] else iteration_number+1 end)
order by Val desc;

解説

replace関数でabを消去する前と後の文字列長の差を、abの文字列長で割って、
文字列abの数を取得してます。

11gならRegexp_Countが使えます。
REGEXP_COUNT - オラクル・Oracle SQL 関数リファレンス

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PL/SQLならowa_patternを使ってもいいでしょう。
10-215 owa_patternで正規表現

SQL> declare
  2      wk ValTable.Val%type;
  3      willOut varchar2(4000);
  4  begin
  5      for rec in (select Val from ValTable) Loop
  6          wk := rec.Val;
  7          willOut := rec.Val || 'のabの数は';
  8          willOut := willOut || OWA_PATTERN.CHANGE(wk,'ab',null,'g');
  9          DBMS_Output.Put_Line(willOut);
 10      end Loop;
 11  end;
 12  /
aのabの数は0
abのabの数は1
ababaaaaのabの数は2
abbabbaabのabの数は3

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
model句で再帰的にinstr関数かRegExp_Instr関数を使う方法の1と2は、
数える文字列がaaaa,abab,acccacなどの場合に、
文字列の登場回数の数え方を理解した上で、使い分けるといいでしょう。

select instr('aaaa','aa',1,1) as a1,
       instr('aaaa','aa',1,2) as a2,
       instr('aaaa','aa',1,3) as a3,
       instr('aaaa','aa',1,4) as a4,
       instr('aaaa','aa',1,5) as a5,
       instr('aaaa','aa',1,6) as a6
from dual ;

A1  A2  A3  A4  A5  A6
--  --  --  --  --  --
 1   2   3   0   0   0

---