MySQLのLIKE句で行を抽出し、マッチした文字列を新しい列として表示する方法

MySQLでLIKE句を使って特定の文字列を含む行を抽出し、さらにそのマッチした文字列自体を新しい列として表示したい場合があります。このような処理には、SUBSTRING()関数とLOCATE()関数を組み合わせて使用します。

ここでは、実際にサンプルテーブルを作成しながら手順を確認していきましょう。

1. サンプルテーブルの作成

まず、以下のようにテーブルを作成します。

mysql> create table DemoTable1872
    (
    Name varchar(20)
    );
Query OK, 0 rows affected (0.00 sec)

2. レコードの挿入

次に、INSERTコマンドを使ってサンプルデータを挿入します。

mysql> insert into DemoTable1872 values('John Doe');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1872 values('Adam Smith');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1872 values('Mitchell Johnson');
Query OK, 1 row affected (0.00 sec)

3. テーブル内容の確認

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

mysql> select * from DemoTable1872;

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

+------------------+
| Name             |
+------------------+
| John Doe         |
| Adam Smith       |
| Mitchell Johnson |
+------------------+
3 rows in set (0.00 sec)

4. LIKE句とSUBSTRING()でマッチした文字列を抽出する

それでは本題です。LIKE句で「John」という文字列を含む行を選択し、同時にSUBSTRING()関数を使ってマッチした文字列を新しい列「NewName」として表示するクエリは次のようになります。

mysql> select Name,
    substring(Name, locate('John', Name), length('John')) as NewName
    from DemoTable1872
    where Name like '%John%';

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

+------------------+---------+
| Name             | NewName |
+------------------+---------+
| John Doe         | John    |
| Mitchell Johnson | John    |
+------------------+---------+
2 rows in set (0.00 sec)

クエリの仕組み解説

このクエリでは、以下の3つの関数が連携して動作しています。

• LOCATE('John', Name): 列「Name」の中で「John」が最初に出現する位置（開始位置）を返します。
• length('John'): 抽出する文字数として検索語の長さ「4」を指定します。
• SUBSTRING(Name, 開始位置, 文字数): 上記2つの情報をもとに、元の文字列から該当部分だけを切り出します。

このようにWHERE句のLIKE条件とSUBSTRING()・LOCATE()・LENGTH()を組み合わせることで、部分一致検索を行いながら、実際にマッチした文字列を別の列として結果セットに含めることができます。検索キーワードが動的に変わる場合にも応用できる便利なテクニックなので、ぜひ覚えておきましょう。