MySQLでレコードのリストから特定の値を検索して置換する方法

MySQLでは、カンマ区切りで保存された文字列の中から特定の値を検索し、別の値に置き換えることができます。本記事では、SUBSTRING_INDEX() 関数と CONCAT() 関数を組み合わせたUPDATE文を使って、名前のリストから特定のレコードを置換する手順を解説します。

テーブルの作成

まず、サンプル用のテーブルを作成します。

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

レコードの挿入

INSERTコマンドを使用して、カンマ区切りの名前リストを含むレコードをテーブルに挿入します。

mysql> insert into DemoTable values('Carol,Sam,John,David,Bob,Mike,Robert,John,Chris,James,Jace');
Query OK, 1 row affected (0.13 sec)

レコードの確認

SELECT文でテーブル内のすべてのレコードを表示します。

mysql> select *from DemoTable;

以下のような出力が得られます。

+------------------------------------------------------------+
| ListOfName                                                 |
+------------------------------------------------------------+
| Carol,Sam,John,David,Bob,Mike,Robert,John,Chris,James,Jace |
+------------------------------------------------------------+
1 row in set (0.00 sec)

検索と置換を行うクエリ

リスト内の2番目の「John」を「Adam」に置き換えるクエリは次のとおりです。

mysql> update DemoTable
    -> set ListOfName=
    -> concat(substring_index(ListOfName,'John',2) ,'Adam', SUBSTRING_INDEX(ListOfName, 'John', -1));
Query OK, 1 row affected (0.37 sec)
Rows matched: 1 Changed: 1 Warnings: 0

仕組みのポイント

SUBSTRING_INDEX(ListOfName,'John',2) は、区切り文字「John」が2回出現するまでの部分文字列を返します。一方、SUBSTRING_INDEX(ListOfName, 'John', -1) は、最後の「John」以降の部分文字列を取得します。この2つの文字列の間に、CONCAT() 関数で新しい値「Adam」を連結することで、指定した位置の値だけを正確に置き換えられます。

結果の確認

もう一度テーブルのレコードを確認してみましょう。

mysql> select *from DemoTable;

実行結果は以下のとおりです。2番目の「John」が「Adam」に置き換わっていることがわかります。

+------------------------------------------------------------+
| ListOfName                                                 |
+------------------------------------------------------------+
| Carol,Sam,John,David,Bob,Mike,Robert,Adam,Chris,James,Jace |
+------------------------------------------------------------+
1 row in set (0.00 sec)