MySQLの正規表現（REGEXP）で特定パターンの列値だけを更新する方法

UPDATE文とREGEXPを組み合わせた条件付き更新

文字列・数値・特殊文字などが混在する列から、特定のパターンに一致する値だけを更新したい場合には、UPDATEコマンドとREGEXP演算子を組み合わせるのが効果的です。ここでは、数値のみで構成される行を判別し、先頭に「Street」という文字列を連結して更新する例を解説します。

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

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

mysql> create table DemoTable2023
    -> (
    -> StreetNumber varchar(100)
    -> );
Query OK, 0 rows affected (0.59 sec)

2. レコードの挿入

INSERTコマンドで数件のデータを登録します。純粋な数値と、アルファベットやハイフンを含む値が混在している点に注目してください。

mysql> insert into DemoTable2023 values('7');
Query OK, 1 row affected (0.17 sec)

mysql> insert into DemoTable2023 values('1');
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoTable2023 values('AUS-100');
Query OK, 1 row affected (0.15 sec)

mysql> insert into DemoTable2023 values('US-101');
Query OK, 1 row affected (0.11 sec)

3. 現在のデータを確認

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

mysql> select *from DemoTable2023;

実行結果は次の通りです。

+--------------+
| StreetNumber |
+--------------+
| 7            |
| 1            |
| AUS-100      |
| US-101       |
+--------------+
4 rows in set (0.00 sec)

4. 正規表現で条件を指定して更新

ここがポイントです。WHERE句にREGEXPを指定し、「1〜2桁の数字のみ」で構成される値を持つ行だけを対象に更新しています。

mysql> update DemoTable2023
    -> set StreetNumber=concat('Street',StreetNumber)
    -> where StreetNumber regexp'^[0-9]{1,2}$';
Query OK, 2 rows affected (0.19 sec)
Rows matched: 2 Changed: 2 Warnings: 0

正規表現「^[0-9]{1,2}$」の各要素は次の意味を持ちます。

• ^：文字列の先頭を表す
• [0-9]：0から9までのいずれか1文字の数字
• {1,2}：直前のパターンが1回または2回繰り返される
• $：文字列の末尾を表す

つまり「7」や「1」のような純粋な数値には一致しますが、「AUS-100」や「US-101」のような英字やハイフンを含む値には一致しません。その結果、数値の行だけがCONCAT関数によって「Street＋元の値」という形式に書き換えられます。

5. 更新結果の確認

もう一度テーブルの中身を確認してみましょう。

mysql> select *from DemoTable2023;

実行結果は以下のようになります。

+--------------+
| StreetNumber |
+--------------+
| Street7      |
| Street1      |
| AUS-100      |
| US-101       |
+--------------+
4 rows in set (0.00 sec)

まとめ

REGEXPをUPDATE文のWHERE句で活用すれば、文字列・数値・特殊文字が混在する列の中から、特定のパターンに一致する行だけを効率的に抽出して更新できます。部分一致ではなく完全一致で判定したい場合は、上の例のように「^」と「$」でパターンを挟むことが重要です。データのクリーニングや一括変換処理において、非常に有用なテクニックなので覚えておきましょう。