MySQLで文字列の一部を置換する：REPLACE関数、SUBSTRING関数、CONCAT関数、正規表現、CASE式を使いこなす

2024-04-02

MySQLで文字列の一部を置換して列の値を更新する方法

MySQLで、特定の列の値の一部を置換して更新するには、UPDATEステートメントと文字列関数 REPLACE() を組み合わせます。

手順

接続するデータベースと更新するテーブルを選択します。
UPDATEステートメントで、更新対象のテーブルと列を指定します。
SET句で、REPLACE()関数を使って、置換後の値を指定します。
WHERE句で、更新対象のレコードを条件指定します。(省略可)
ステートメントを実行します。

例

# テーブル: users
# カラム: name

-- "田中" を "佐藤" に置換
UPDATE users
SET name = REPLACE(name, '田中', '佐藤')
WHERE name = '田中';

解説

UPDATE users: usersテーブルを更新
SET name = REPLACE(name, '田中', '佐藤'): name列の値を、REPLACE()関数を使って置換
- name: 置換対象の文字列を含む列
- '田中': 置換される文字列
WHERE name = '田中': name列が"田中"であるレコードのみ更新

その他の置換方法

UPDATEステートメントと文字列関数 SUBSTRING()、CONCAT()を組み合わせて、部分文字列を置換することもできます。
正規表現を使って、より複雑な置換を行うこともできます。

# テーブル: users
# カラム: name

-- "田中" を "佐藤" に置換
UPDATE users
SET name = REPLACE(name, '田中', '佐藤')
WHERE name = '田中';

説明

この例では、usersテーブルのname列で、"田中"という文字列を"佐藤"に置換します。WHERE句によって、name列が"田中"であるレコードのみ更新されます。

例2：部分文字列を置換する

# テーブル: products
# カラム: description

-- 商品名の先頭にある "【特価】" を削除
UPDATE products
SET description = REPLACE(description, '【特価】', '');

この例では、productsテーブルのdescription列で、商品名の先頭にある"【特価】"という文字列を削除します。

例3：正規表現を使って置換する

# テーブル: articles
# カラム: content

-- URLをすべてリンクに変換
UPDATE articles
SET content = REPLACE(content, 
  '(https?://[\\w/:%#\\$\\?\\+\\~\\-]*@)?[\\w\\.-]+\\.[\\w]{2,6}([\\w/:%#\\$\\?\\+\\~\\-]*)?', 
  '<a href="$1">$1</a>');

この例では、articlesテーブルのcontent列で、URLをすべてリンクに変換します。正規表現を使って、URLのパターンに一致する文字列を置換しています。

注意事項

置換する文字列と置換後の文字列が同じ場合、何も置換されません。
置換する文字列が存在しない場合、エラーが発生します。
正規表現を使う場合は、パターンに誤りがないことを確認してください。

MySQLで文字列の一部を置換する他の方法

REPLACE()関数以外にも、SUBSTRING()とCONCAT()関数を使って文字列の一部を置換することができます。

# テーブル: users
# カラム: name

-- "田中" を "佐藤" に置換
UPDATE users
SET name = CONCAT(SUBSTRING(name, 1, 2), '佐藤', SUBSTRING(name, 4));
WHERE name = '田中';

この例では、SUBSTRING()関数を使って、"田中"という文字列の最初の2文字と最後の2文字を抽出します。そして、CONCAT()関数を使って、抽出された文字列と"佐藤"という文字列を結合して、新しい文字列を作成します。

# テーブル: articles
# カラム: content

-- 数字をすべて"*" に置換
UPDATE articles
SET content = REGEXP_REPLACE(content, '[0-9]+', '*');

この例では、正規表現 [0-9]+ を使って、content列の数字をすべて"*"に置換します。

CASE式を使って、置換する文字列を条件によって分岐させることができます。

# テーブル: products
# カラム: status

-- ステータスコードを文字列に変換
UPDATE products
SET status = CASE status
  WHEN 1 THEN '販売中'
  WHEN 2 THEN '在庫切れ'
  ELSE '販売終了'
END;