はじめに
よく忘れるため、自分用のメモとして残しておきます。
SQLでは、指定した文字列の中に特定の文字がいくつ含まれているかをカウントするには、一般的に 「元の文字列の長さ」から「特定の文字を除去した後の文字列の長さ」を引く という手法を使います。
-
基本:
(元の長さ - 除去後の長さ) -
複数文字の場合:
(元の長さ - 除去後の長さ) / 除去した文字列の長さ
※この考え方はDBMSに依存しない共通のテクニックであり、関数名や記法が異なるだけで、MySQLやPostgreSQLなどでも同様に利用できます。
サンプル
Oracleの場合、LENGTH 関数で文字数を取得できます。対象が複数文字（例: 'apple' に含まれる 'pp' など）の場合は、少し工夫が必要です。
単一文字をカウントする場合
SELECT LENGTH('banana') - LENGTH(REPLACE('banana', 'a', '')) FROM dual;
-- 結果: 3
複数文字（文字列）をカウントする場合
単純に REPLACE を使うだけでは文字数の差分がそのまま出現回数にならないため、除去した文字列の長さで割る必要があります。
※この方法は単一文字のカウントにも汎用的に利用できます。
SELECT (LENGTH('banana') - LENGTH(REPLACE('banana', 'na', ''))) / LENGTH('na') FROM dual;
-- 結果: 2 （'na' が2回出現）
補足
この方法は 重複しない出現（非オーバーラップ） のカウントです。
たとえば 'aaa' に対して 'aa' を数える場合、結果は 1 になります（2 にはなりません）。
また、繰り返しになりますが、このテクニック自体はSQLの方言に依存しません。
MySQLやPostgreSQLでも、LENGTH や REPLACE に相当する関数を使えば、同じロジックで実現できます。
環境
- Oracle Database 19c