MySQLのREPLACE関数で列の値に含まれる文字列を一括置換する方法

はじめに

MySQLでは、UPDATE文とREPLACE()関数を組み合わせることで、テーブルの列に含まれる特定の文字列(特殊文字など)を簡単に一括置換できます。この記事では、実際のサンプルコードを使ってその手順をわかりやすく解説します。

サンプルテーブルの作成

まず、以下のコマンドでテーブルを作成します。

mysql> create table DemoTable1574
    -> (
    -> StudentCode varchar(20)
    -> );
Query OK, 0 rows affected (0.59 sec)

レコードの挿入

次に、INSERTコマンドを使ってテーブルにいくつかのレコードを挿入します。ここには、通常の値とアンダースコアのみで構成された値が混在しています。

mysql> insert into DemoTable1574 values('111_Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1574 values('______');
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable1574 values('David_12345');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1574 values('______');
Query OK, 1 row affected (0.12 sec)

現在のデータを確認する

SELECT文でテーブル内の全レコードを表示してみましょう。

mysql> select * from DemoTable1574;

実行すると、以下のような出力が得られます。

+-------------+
| StudentCode |
+-------------+
| 111_Carol   |
| ______      |
| David_12345 |
| ______      |
+-------------+
4 rows in set (0.00 sec)

文字列を置き換えるクエリ

ここで、「______」を「Mike_007」に置き換えるのが、以下のクエリです。

mysql> update DemoTable1574 set StudentCode=replace(StudentCode,'______','Mike_007');
Query OK, 2 rows affected (0.38 sec)
Rows matched: 4  Changed: 2 Warnings: 0

replace(列名, '置換前の文字列', '置換後の文字列')という形式で指定することで、該当するすべての行が一度に更新されます。この例では、4件中2件のレコードが変更されました。

更新結果の確認

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

mysql> select * from DemoTable1574;

実行すると、以下のような出力が得られます。

+-------------+
| StudentCode |
+-------------+
| 111_Carol   |
| Mike_007    |
| David_12345 |
| Mike_007    |
+-------------+
4 rows in set (0.00 sec)

まとめ

このように、UPDATE文とREPLACE()関数を組み合わせれば、テーブル全体を対象に特定の文字列を効率的に置き換えられます。アンダースコアなどの特殊文字や不要なプレフィックス・サフィックスの一括修正に、ぜひ活用してみてください。