REGEXP_REPLACE 関数
文字列で正規表現パターンを検索し、このパターンのすべての出現を特定の文字列に置き換えます。REGEXP_REPLACE は REPLACE 関数 関数に似ていますが、文字列で正規表現パターンを検索することができます。正規表現の詳細については、Wikipedia の「POSIX 演算子」と「正規表現
REGEXP_REPLACE は、TRANSLATE 関数や REPLACE 関数 と似ています。ただし、TRANSLATE は複数の単一文字置換を行い、REPLACE は 1 つの文字列全体を別の文字列に置換しますが、REGEXP_REPLACE を使用すると正規表現パターンの文字列を検索できます。
構文
REGEXP_REPLACE( source_string, pattern [, replace_string [ , position [, parameters ] ] ] )
引数
- source_string
-
検索する
CHARまたはVARCHAR文字列式 (列名など)。 - pattern
-
正規表現パターンを表す UTF-8 文字列リテラル。詳細については、「POSIX 演算子」を参照してください。
- replace_string
-
(オプション) パターンのすべての出現に置き換わる列名などの
CHARまたはVARCHAR文字列式。デフォルトは空の文字列 ("") です。 - position
-
(オプション) 検索を開始する source_string 内の位置を示す正の整数。position はバイト数ではなく文字数に基づくため、マルチバイト文字は 1 文字としてカウントされます。デフォルト:
1。position が1より小さい場合、source_string の最初の文字から検索が開始されます。position が source_string の文字数より大きい場合、結果は source_string になります。 - parameters
-
(オプション) 関数がパターンとどのように一致するかを示す 1 つ以上のリテラル文字列。取り得る値には以下のものがあります。
-
c – 大文字と小文字を区別する一致を実行します。デフォルトでは大文字と小文字を区別するマッチングを使用します。
-
i – 大文字と小文字を区別しない一致を実行します。
-
p – Perl 互換正規表現 (PCRE) 言語でパターンを解釈します。PCRE の詳細については、Wikipedia の「Perl 互換の正規表現
」を参照してください。
-
戻り型
VARCHAR
pattern または replace_string のいずれかが NULL の場合、戻り値は NULL を返します。
例
大文字と小文字を区別しない一致を使用して、値 quick brown fox 内の文字列 FOX のすべての出現を置き換えるには、次の例を使用します。
SELECT REGEXP_REPLACE('the fox', 'FOX', 'quick brown fox', 1, 'i');+---------------------+ | regexp_replace | +---------------------+ | the quick brown fox | +---------------------+
次の例では、PCRE 言語で記述されたパターンを使用して、少なくとも 1 つの数字と 1 つの小文字を含む単語を検索します。PCRE で特定の先読みの意味を持つ ?= 演算子を使用します。そのような単語が出現するたびに値 [hidden] に置き換えるには、次の例を使用します。
SELECT REGEXP_REPLACE('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', '[hidden]', 1, 'p');+-------------------------------+ | regexp_replace | +-------------------------------+ | [hidden] plain A1234 [hidden] | +-------------------------------+
次の例では、PCRE 言語で記述されたパターンを使用して、少なくとも 1 つの数字と 1 つの小文字を含む単語を検索します。PCRE で特定の先読みの意味を持つ ?= 演算子を使用します。そのような単語が出現するたびに値 [hidden] に置き換えるが、大文字と小文字を区別しない一致を使用することが前の例とは異なる場合は、次の例を使用します。
SELECT REGEXP_REPLACE('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', '[hidden]', 1, 'ip');+----------------------------------+ | regexp_replace | +----------------------------------+ | [hidden] plain [hidden] [hidden] | +----------------------------------+
次の例では、TICKIT サンプルデータベースの USERS テーブルからのデータを使用します。詳細については、「サンプルデータベース」を参照してください。
E メールアドレスから @ とドメイン名を削除するには、次の例を使用します。
SELECT email, REGEXP_REPLACE(email, '@.*\\.(org|gov|com|edu|ca)$') FROM users ORDER BY userid LIMIT 4;+-----------------------------------------------+-----------------------+ | email | regexp_replace | +-----------------------------------------------+-----------------------+ | Etiam.laoreet.libero@sodalesMaurisblandit.edu | Etiam.laoreet.libero | | Suspendisse.tristique@nonnisiAenean.edu | Suspendisse.tristique | | amet.faucibus.ut@condimentumegetvolutpat.ca | amet.faucibus.ut | | sed@lacusUtnec.ca | sed | +-----------------------------------------------+-----------------------+
E メールアドレスのドメイン名を internal.company.com に置き換えるには、次の例を使用します。
SELECT email, REGEXP_REPLACE(email, '@.*\\.[[:alpha:]]{2,3}','@internal.company.com') FROM users ORDER BY userid LIMIT 4;+-----------------------------------------------+--------------------------------------------+ | email | regexp_replace | +-----------------------------------------------+--------------------------------------------+ | Etiam.laoreet.libero@sodalesMaurisblandit.edu | Etiam.laoreet.libero@internal.company.com | | Suspendisse.tristique@nonnisiAenean.edu | Suspendisse.tristique@internal.company.com | | amet.faucibus.ut@condimentumegetvolutpat.ca | amet.faucibus.ut@internal.company.com | | sed@lacusUtnec.ca | sed@internal.company.com | +-----------------------------------------------+--------------------------------------------+
