MySQLで特定の文字を無視してパターン検索！Chris.Brownのような名前も一致させる方法

概要：区切り文字の違いを無視して名前を検索するには？

「Chris Brown」（スペース区切り）と「Chris.Brown」（ピリオド区切り）のように、区切り文字が違うだけで実質的に同じ名前を指すデータがテーブル内に混在していることがあります。単純な完全一致検索では、こうした表記ゆらぎを持つレコードを取得できません。
本記事では、MySQLでピリオドなどの特定の文字を無視して検索を実行する方法を、実際のSQL例とともにわかりやすく解説します。

サンプルテーブルを作成する

まず、動作確認用のテーブルを作成します。

mysql> create table DemoTable
(
    Name varchar(40)
)
;
Query OK, 0 rows affected (0.63 sec)

テストデータを挿入する

insertコマンドを使って、ピリオド区切りとスペース区切りの両方の名前を含むレコードを登録します。

mysql> insert into DemoTable values('John.Smith');
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable values('Chris Brown');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Chris.Brown');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('David Miller');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('Chris.Taylor');
Query OK, 1 row affected (0.18 sec)

登録されたデータを確認する

select文ですべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

実行結果は次のとおりです。

+--------------+
| Name         |
+--------------+
| John.Smith   |
| Chris Brown  |
| Chris.Brown  |
| David Miller |
| Chris.Taylor |
+--------------+
5 rows in set (0.00 sec)

REPLACE関数で文字を無視して検索する

正規表現（REGEXP）でも同様の処理は可能ですが、この用途ではREPLACE関数を使うのが最もシンプルで確実です。カラムの値からピリオドを取り除いてから比較することで、記号の有無に影響されない検索が行えます。

mysql> select *from DemoTable where replace(Name,'.','')='Chris Brown';

実行結果：

+-------------+
| Name        |
+-------------+
| Chris Brown |
+-------------+
1 row in set (0.00 sec)

応用：スペースとピリオドの両方を無視してマッチさせる

上記のクエリはカラム側のピリオドしか除去していないため、「Chris.Brown」という表記はヒットしません。スペースやピリオドなど複数の文字をまとめて無視したい場合は、REPLACEを入れ子にしてすべて除去してから比較します。

mysql> select *from DemoTable
    -> where replace(replace(Name,'.',''),' ','') = 'ChrisBrown';

このクエリでは、「Chris Brown」も「Chris.Brown」もどちらも「ChrisBrown」という同じ形に正規化されて比較されるため、次のように両方のレコードを一度に取得できます。

+-------------+
| Name        |
+-------------+
| Chris Brown |
| Chris.Brown |
+-------------+
2 rows in set (0.00 sec)

MySQL 8.0以降ならREGEXP_REPLACEも便利

MySQL 8.0以降を使用している場合は、正規表現で置換を行えるREGEXP_REPLACE関数が利用可能です。文字クラス「[ .]」を指定すれば、スペースとピリオドを一度に除去できます。

mysql> select *from DemoTable where regexp_replace(Name, '[ .]', '') = 'ChrisBrown';

まとめ

検索時に特定の文字を無視したい場合は、REPLACE関数（MySQL 8.0以降ならREGEXP_REPLACE関数）で比較前に不要な文字を取り除くのが効果的です。
なお、WHERE句内で関数を使用するとインデックスが利用できず、大規模なテーブルでは検索が遅くなる可能性があります。その場合は、正規化後の値を格納する生成カラム（GENERATED COLUMN）にインデックスを作成するなど、パフォーマンス面への対策も併せて検討するとよいでしょう。