【初心者向け】MySQLで正規表現置換を行う方法：REGEXP_REPLACE関数、UPDATE文、UDFを使いこなす

MySQLで正規表現置換を行う方法

本記事では、MySQLで正規表現置換を行う方法について、以下の3つの方法を中心に解説します。

1. REGEXP_REPLACE関数を使う
2. UPDATE文とSUBSTRING_INDEX関数を使う
3. MySQL UDFを使う

それぞれの特徴やメリット・デメリットを比較しながら、自分に合った方法を選べるように説明していきます。

REGEXP_REPLACE関数を使う

REGEXP_REPLACE関数は、正規表現でマッチした部分文字列を置換する関数です。

構文:

REGEXP_REPLACE(str, pattern, replacement [, flags])

引数:

• str: 置換対象の文字列
• pattern: 置換パターンを表す正規表現
• replacement: 置換後の文字列
• flags: オプション (省略可能)

例:

# 全ての数字を"*"に置換
SELECT REGEXP_REPLACE('abc123def456', '[0-9]', '*');
# 結果: "abc***def***"

# "abc" を "XYZ" に置換
SELECT REGEXP_REPLACE('abc123def456', 'abc', 'XYZ');
# 結果: "XYZ123def456"

メリット:

• シンプルで分かりやすい
• 豊富なオプションで細かい置換処理が可能

• 正規表現の知識が必要
• 複雑なパターンになると処理速度が遅くなる場合がある

UPDATE文とSUBSTRING_INDEX関数を使う

UPDATE文とSUBSTRING_INDEX関数を組み合わせることで、部分文字列を置換することができます。

UPDATE table
SET column = SUBSTRING_INDEX(column, 'abc', '')
WHERE column LIKE '%abc%';

この例では、tableテーブルのcolumnカラムにおいて、"abc"を含む全ての文字列を""(空文字)に置換します。

• 正規表現の知識がなくても使える

• 置換パターンが複雑になると記述が煩雑になる
• REGEXP_REPLACE関数に比べて処理速度が遅くなる場合がある

MySQL UDFを使う

MySQL UDFは、ユーザー定義関数の略で、MySQLにない機能を拡張するために作成できるプログラムです。

正規表現置換用のUDFを作成することで、より柔軟で効率的な置換処理を行うことができます。

• 複雑な置換パターンにも対応できる
• 処理速度を高速化できる

• UDFの開発知識が必要
• MySQLのバージョンによってはUDFが使えない場合がある

MySQLで正規表現置換を行う方法はいくつかあり、それぞれにメリットとデメリットがあります。

REGEXP_REPLACE関数を使う

-- 全ての数字を"*"に置換
SELECT REGEXP_REPLACE('abc123def456', '[0-9]', '*');

-- "abc" を "XYZ" に置換
SELECT REGEXP_REPLACE('abc123def456', 'abc', 'XYZ');

-- 行頭に空白がある場合、削除
SELECT REGEXP_REPLACE('   abc123', '^\\s+', '');

-- 行末に空白がある場合、削除
SELECT REGEXP_REPLACE('abc123   ', '\\s+$', '');

-- メールアドレスからドメイン部分のみ抽出
SELECT REGEXP_EXTRACT('[email protected]', '@.*');

UPDATE文とSUBSTRING_INDEX関数を使う

-- テーブル `users` の `email` カラムにおいて、
-- "example.com" を "gmail.com" に置換
UPDATE users
SET email = SUBSTRING_INDEX(email, '@example.com', '') || '@gmail.com'
WHERE email LIKE '%@example.com%';

-- テーブル `products` の `name` カラムにおいて、
-- 最初の空白文字以降を全て削除
UPDATE products
SET name = SUBSTRING_INDEX(name, ' ', 1);

MySQL UDFを使う

DELIMITER //

CREATE FUNCTION swap_case(str VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
  DECLARE i INT;
  DECLARE new_str VARCHAR(255);
  SET new_str = '';
  FOR i := 1 TO LENGTH(str) DO
    IF ascii(SUBSTRING(str, i, 1)) >= 65 AND ascii(SUBSTRING(str, i, 1)) <= 90 THEN
      SET new_str = CONCAT(new_str, LOWER(SUBSTRING(str, i, 1)));
    ELSE
      SET new_str = CONCAT(new_str, UPPER(SUBSTRING(str, i, 1)));
    END IF;
  END FOR;
  RETURN new_str;
END;

//

SELECT swap_case('Hello, World!');

この例では、swap_caseという名前のUDFを作成し、大文字と小文字を入れ替えています。

上記のサンプルコードはあくまでも例です。

MySQLで正規表現置換を行うその他の方法

以下に、その他の方法とそれぞれの特徴を紹介します。

REPLACE関数は、指定した文字列を別の文字列に置換する関数です。

REPLACE(str, old_str, new_str)

# 全ての"a"を"*"に置換
SELECT REPLACE('abc', 'a', '*');
# 結果: "*bc"

• 正規表現が使えない

LIKE演算子を使う

LIKE演算子は、文字列がパターンと一致するかどうかを判断する演算子です。

この演算子を利用して、パターンに一致する部分文字列を置換することができます。

SELECT CONCAT(LEFT(column, 3), '*', RIGHT(column, 3))
FROM table
WHERE column LIKE '%abc%';

Perl 互換正規表現を使う

MySQL 8.0以降では、Perl 互換正規表現を使用することができます。

ストアドプロシージャを使う

ストアドプロシージャは、SQL文の塊をまとめて実行できる機能です。