MySQLでVARCHARフィールド内の文字列出現回数をカウントする方法

MySQLでVARCHARフィールド内の文字列出現回数をカウントする方法

VARCHAR型のフィールド内に特定の文字列が何回出現しているかをカウントしたい場合、「元の文字列の長さ」から「対象文字列を削除した後の長さ」を引き、その差を「対象文字列の長さ」で割るというシンプルなロジックを活用できます。

まず、CREATE文を使ってサンプル用のテーブルを作成しましょう。

mysql> create table StringOccurrenceDemo
   -> (
   -> Cases varchar(100),
   -> StringValue varchar(500)
   -> );
Query OK, 0 rows affected (0.56 sec)

テストデータの挿入

テーブルを作成したら、INSERT文でテストデータを挿入します。以下のクエリを実行してください。

mysql> insert into StringOccurrenceDemo values('First','This is MySQL Demo and MySQL is an open source RDBMS');
Query OK, 1 row affected (0.07 sec)

mysql> insert into StringOccurrenceDemo values('Second','There is no');
Query OK, 1 row affected (0.20 sec)

mysql> insert into StringOccurrenceDemo values('Third','There is MySQL,Hi MySQL,Hello MySQL');
Query OK, 1 row affected (0.17 sec)

登録データの確認

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

mysql> select * From StringOccurrenceDemo;

実行結果は以下の通りです。

+--------+------------------------------------------------------+
| Cases  | StringValue                                          |
+--------+------------------------------------------------------+
| First  | This is MySQL Demo and MySQL is an open source RDBMS |
| Second | There is no                                          |
| Third  | There is MySQL,Hi MySQL,Hello MySQL                  |
+--------+------------------------------------------------------+
3 rows in set (0.00 sec)

文字列「MySQL」の出現回数をカウントするクエリ

ここからが本題です。LENGTH関数とREPLACE関数を組み合わせることで、文字列「MySQL」の出現回数を求められます。結果は「NumberOfOccurrenceOfMySQL」というカラム名で表示されます。

mysql> SELECT Cases,StringValue,
   -> ROUND (
   -> (
   -> LENGTH(StringValue)- LENGTH( REPLACE (StringValue, "MySQL", "") )
   -> ) / LENGTH("MySQL")
   ->  ) AS NumberOfOccurrenceOfMySQL
   -> from StringOccurrenceDemo;

実行結果は以下の通りです。

+--------+------------------------------------------------------+---------------------------+
| Cases  | StringValue                                          | NumberOfOccurrenceOfMySQL |
+--------+------------------------------------------------------+---------------------------+
| First  | This is MySQL Demo and MySQL is an open source RDBMS |                         2 |
| Second | There is no                                          |                         0 |
| Third  | There is MySQL,Hi MySQL,Hello MySQL                  |                         3 |
+--------+------------------------------------------------------+---------------------------+
3 rows in set (0.05 sec)

上記の出力から、「First」には2回、「Second」には0回、「Third」には3回の「MySQL」が含まれていることが確認できました。

クエリの仕組みを解説

このカウントロジックは、次の手順で成り立っています。

1. LENGTH(StringValue)：元の文字列全体の長さを取得します。
2. LENGTH(REPLACE(StringValue, "MySQL", ""))：「MySQL」を空文字に置換した後の長さを取得します。
3. 両者の差分＝「MySQL」が出現した回数分だけ短くなった長さになります。
4. その差をLENGTH("MySQL")（5文字）で割れば、出現回数が算出できます。

注意点：マルチバイト文字を扱う場合

LENGTH関数はバイト数を返すため、日本語などのマルチバイト文字が含まれるデータでは意図しない結果になる可能性があります。その場合は、文字数をカウントするCHAR_LENGTH関数に置き換えることで、どの言語の文字列でも正確に出現回数を求められます。