MySQL、MariaDBで使える！REGEXP_REPLACEの便利な機能
REGEXP_REPLACE ガイダンス：MySQL、Regex、MariaDB
REGEXP_REPLACEは、MySQL、MariaDB、およびその他のデータベースで使用される関数で、テキスト内のパターンを置換するために使用されます。正規表現（regex）を使用して、置換するパターンを指定できます。
構文
REGEXP_REPLACE(
str,
pattern,
replacement,
[flags]
)
引数
- str: 置換対象となるテキスト
- pattern: 置換するパターンを指定する正規表現
- replacement: 置換後のテキスト
- flags: オプションのフラグ。詳細は後述
例
# すべての数字を "X" に置換
SELECT REGEXP_REPLACE('abc123def', '[0-9]', 'X');
# 結果: abcXXXdef
# 最初の "a" を "A" に置換
SELECT REGEXP_REPLACE('abc', 'a', 'A', 1);
# 結果: Aabc
# 大文字と小文字を区別せずに "a" を "A" に置換
SELECT REGEXP_REPLACE('abc', 'a', 'A', 'i');
# 結果: AabC
正規表現は、パターンを記述するための強力なツールです。詳細は 正規表現チュートリアル: URL 正規表現チュートリアル を参照してください。
フラグ
以下のフラグが使用できます。
- i: 大文字と小文字を区別しない
- g: すべてのマッチング箇所を置換する
- m: マルチラインモード。^ と $ が行頭に/末尾だけでなく、各行の頭に/末尾にもマッチする
- e: replacement を式として評価する
MariaDB では、以下の拡張機能が使用できます。
- p: 置換後のテキストを、パターンと同じ位置に挿入する
# 最初の "a" の後に "A" を挿入
SELECT REGEXP_REPLACE('abc', 'a', 'A', 'p');
# 結果: abCAc
# 最初の "a" を "A" に置換し、その後に "B" を挿入
SELECT REGEXP_REPLACE('abc', 'a', 'A|B', 'p');
# 結果: ABabC
- MySQL REGEXP_REPLACE: URL MySQL REGEXP_REPLACE
- 正規表現チュートリアル: URL 正規表現チュートリアル
補足
- REGEXP_REPLACE は、複雑なパターンを置換する際に非常に強力なツールです。
- 正規表現を理解していない場合は、誤った結果になる可能性があります。
# すべての数字を "X" に置換
SELECT REGEXP_REPLACE('abc123def', '[0-9]', 'X');
# 結果: abcXXXdef
最初の "a" を "A" に置換
# 最初の "a" を "A" に置換
SELECT REGEXP_REPLACE('abc', 'a', 'A', 1);
# 結果: Aabc
大文字と小文字を区別せずに "a" を "A" に置換
# 大文字と小文字を区別せずに "a" を "A" に置換
SELECT REGEXP_REPLACE('abc', 'a', 'A', 'i');
# 結果: AabC
# 最初の "a" の後に "A" を挿入
SELECT REGEXP_REPLACE('abc', 'a', 'A', 'p');
# 結果: abCAc
最初の "a" を "A" に置換し、その後に "B" を挿入
# 最初の "a" を "A" に置換し、その後に "B" を挿入
SELECT REGEXP_REPLACE('abc', 'a', 'A|B', 'p');
# 結果: ABabC
メールアドレスの検証
# メールアドレスの検証
SELECT REGEXP_REPLACE('[email protected]', '^[^@]+@[^@]+\.[^@]+$', 'Valid email');
# 結果: Valid email
HTML タグの除去
# HTML タグの除去
SELECT REGEXP_REPLACE('<p>This is some text.</p>', '<[^>]+>', '');
# 結果: This is some text.
URL の抽出
# URL の抽出
SELECT REGEXP_REPLACE('This is a URL: https://www.example.com', 'https?://[^ ]+', '\\1');
# 結果: https://www.example.com
上記のサンプルコードは、REGEXP_REPLACE 関数の様々な機能を
REGEXP_REPLACE 以外の方法
REPLACE 関数は、特定の文字列を別の文字列に置換するシンプルな関数です。正規表現を使用できない場合や、簡単な置換を行う場合に便利です。
# すべての "a" を "A" に置換
SELECT REPLACE('abc', 'a', 'A');
# 結果: AabC
SUBSTRING 関数と INSTR 関数を組み合わせて、パターンを置換することができます。ただし、複雑なパターンを置換するには、複雑なコードを書く必要があり、可読性が悪くなる可能性があります。
# 最初の "a" を "A" に置換
SELECT SUBSTRING('abc', 1, INSTR('abc', 'a') - 1)
|| 'A'
|| SUBSTRING('abc', INSTR('abc', 'a') + 1);
# 結果: AabC
CASE WHEN ステートメントを使用して、パターンごとに異なる置換を行うことができます。ただし、パターンが多くなると、コードが冗長になり、可読性が悪くなる可能性があります。
SELECT
CASE WHEN str LIKE '%a%' THEN REPLACE(str, 'a', 'A')
ELSE str
END
FROM your_table;
外部ライブラリ
MySQL や MariaDB には、正規表現処理用の外部ライブラリが用意されています。これらのライブラリを使用すると、より複雑なパターンをより簡単に置換することができます。
# MySQLの場合
# https://dev.mysql.com/doc/refman/8.0/en/regexp.html
SELECT REGEXP_REPLACE(str, pattern, replacement, 'm', 'g', 'perl');
# MariaDBの場合
# https://mariadb.com/kb/en/library/regexp-functions/
SELECT REGEXP_REPLACE(str, pattern, replacement, 'm', 'g', 'PCRE');
方法の選択
どの方法を選択するかは、置換するパターンの複雑さや、コードの可読性などを考慮する必要があります。
- 簡単な置換の場合は、REPLACE 関数がおすすめです。
- 複雑なパターンを置換する場合は、REGEXP_REPLACE 関数を使用するのが一般的です。
- さらに複雑なパターンを置換する場合は、外部ライブラリを使用することを検討してください。
mysql regex mariadb
