MySQLで特定の文字列の出現回数を調べる方法

MySQLである文字列の中に、特定の文字列が何回出現するかを調べるには、LENGTH()関数を活用するのが便利です。ここでは、カンマ区切りの値が格納された文字列の中に「10」がいくつ含まれているかを数える例を紹介します。

サンプルテーブルの作成

まずはCREATE TABLE文でテーブルを作成しましょう。

mysql> create table DemoTable
-> (
-> Value text
-> );
Query OK, 0 rows affected (0.74 sec)

レコードの挿入

続いて、INSERT文でサンプルデータを登録します。

mysql> insert into DemoTable values('10,20,10,30,10,40,50,40');
Query OK, 1 row affected (0.24 sec)

登録内容の確認

SELECT文ですべてのレコードを表示して、正しく登録されているか確認しておきます。

mysql> select *from DemoTable;

出力結果

+-------------------------+
| Value                   |
+-------------------------+
| 10,20,10,30,10,40,50,40 |
+-------------------------+
1 row in set (0.00 sec)

特定の文字列の出現回数を取得するクエリ

以下が、MySQLで特定の文字列の出現回数を調べるためのクエリです。この例では、文字列「10」が何回出現するかを取得しています。

mysql> select LENGTH(Value) + 2 - LENGTH(REPLACE(CONCAT(',', Value, ','), ',10,', 'len')) from DemoTable;

出力結果

+----------------------------------------------------------------------------+
| LENGTH(Value) + 2 - LENGTH(REPLACE(CONCAT(',', Value, ','), ',10,', 'len')) |
+----------------------------------------------------------------------------+
| 3                                                                          |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

実行結果から、値「10,20,10,30,10,40,50,40」の中に「10」が3回出現していることがわかります。

クエリの仕組み

このクエリは、次のような流れで出現回数を算出しています。

1. 前後にカンマを付ける：CONCAT(',', Value, ',') で文字列の先頭と末尾にカンマを追加し、「10」が文字列の端にあった場合でも必ず「,10,」という形でマッチするようにしています。
2. 置換する：REPLACE()で「,10,」を3文字の「len」に置き換えます。1回の置換につき文字数は4文字から3文字へと1文字ずつ減ります。
3. 文字数の差を計算する：元の文字数（追加したカンマ2文字分を加算）から置換後の文字数を引くと、その差がそのまま出現回数になります。

なお、単純に「10」という文字だけを検索すると、「110」や「210」など別の数値の一部まで誤ってカウントされるおそれがあります。カンマを挟んだ「,10,」でマッチさせるこの方法なら、そのような誤カウントを防げるのが大きなメリットです。