カテゴリ：

文字列関数（正規表現）

REGEXP_REPLACE

指定されたパターン（またはパターンのすべての出現）が削除されたか、置換文字列に置き換えられた対象を返します。

構文

 REGEXP_REPLACE( <subject> ,
                 <pattern>
                   [ , <replacement>
                     [ , <position>
                       [ , <occurrence>
                         [ , <parameters> ]
                       ]
                     ]
                   ]
)

引数

必須：

subject

一致を検索する文字列です。

pattern

一致するパターンです。

パターンの指定に関するガイドラインについては、 文字列関数（正規表現） をご参照ください。

オプション：

replacement

パターンと一致したサブ文字列を置き換える文字です。空の文字列が指定されている場合、関数は一致したパターンをすべて削除し、結果の文字列を返します。

デフォルト： '' （空の文字列）。

position

関数が一致の検索を開始する文字列の先頭からの文字数です。値は正の整数にする必要があります。

デフォルト： 1 （一致の検索は左側の最初の文字から始まります）

occurrence

置換するパターンの出現を指定します。0 が指定されている場合、すべての出現箇所が置き換えられます。

デフォルト： 0 （すべての出現）

parameters

一致の検索に使用されるパラメーターを指定する1つ以上の文字の文字列です。サポートされている値:

パラメーター

説明

c

大文字と小文字を区別する一致

i

大文字と小文字を区別しない一致

m

複数行モード

e

部分一致を抽出

s

単一行モード POSIX ワイルドカード文字 . 一致 \n

デフォルト： c

詳細については、 正規表現のパラメーターの指定 をご参照ください。

戻り値

VARCHAR 型の値を返します。

一致するものが見つからない場合、元のサブジェクトを返します。

いずれかの引数が NULL の場合は NULL を返します。

使用上の注意

  • 置換文字列には、グループをキャプチャするためのバックリファレンス（例えば、パターンのサブ式）を含めることができます。キャプチャグループは、括弧（( )）で囲まれた正規表現です。キャプチャグループの最大数は9です。

    バックリファレンスは、キャプチャグループ内の式と一致します。バックリファレンスの形式は n で、 n は0から9までの値であり、キャプチャグループの一致するインスタンスを参照します。詳細については、 例 （このトピック内）をご参照ください。

  • 現在、括弧（( )）と角括弧（[ ]）は、リテラル文字列として解析するために二重エスケープする必要があります。

    次の例は、括弧を削除する方法を示しています。

    SELECT REGEXP_REPLACE('Customers - (NY)','\\(|\\)','') AS customers;

    +----------------+
| CUSTOMERS      |
|----------------|
| Customers - NY |
+----------------+

  • 追加の使用上の注意については、正規表現関数の 一般的な使用上の注意 をご参照ください。

照合順序の詳細

Arguments with collation specifications currently aren't supported.

例

次の例では、文字列内のすべてのスペースをスペース無しに置き換えます（つまり、すべてのスペースが削除されます）。

SELECT REGEXP_REPLACE('It was the best of times, it was the worst of times',
                      '( ){1,}',
                      '') AS result;

+------------------------------------------+
| RESULT                                   |
|------------------------------------------|
| Itwasthebestoftimes,itwastheworstoftimes |
+------------------------------------------+

次の例では、文字列 times をマッチングし、文字列 days に置き換えます。マッチングは、文字列内の最初の文字から始まり、部分文字列の2番目の出現を置き換えます。

SELECT REGEXP_REPLACE('It was the best of times, it was the worst of times',
                      'times',
                      'days',
                      1,
                      2) AS result;

+----------------------------------------------------+
| RESULT                                             |
|----------------------------------------------------|
| It was the best of times, it was the worst of days |
+----------------------------------------------------+

次の例では、後方参照を使用して文字列 firstname middlename lastname を lastname, firstname middlename として再配置し、 lastname と firstname の間にコンマを挿入します。

SELECT REGEXP_REPLACE('firstname middlename lastname',
                      '(.*) (.*) (.*)',
                      '\\3, \\1 \\2') AS name_sort;

+--------------------------------+
| NAME_SORT                      |
|--------------------------------|
| lastname, firstname middlename |
+--------------------------------+

残りの例では、以下のテーブルデータを使用します。

CREATE OR REPLACE TABLE regexp_replace_demo(body VARCHAR(255));

INSERT INTO regexp_replace_demo values
  ('Hellooo World'),
  ('How are you doing today?'),
  ('the quick brown fox jumps over the lazy dog'),
  ('PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS');

次の例では、任意の2文字間の一致を検索する空のグループ（()）を使用して、開始と終了を含む、対象のすべての文字間に文字``*``を挿入します。

SELECT body,
       REGEXP_REPLACE(body, '()', '*') AS replaced
  FROM regexp_replace_demo;

+---------------------------------------------+-----------------------------------------------------------------------------------------+
| BODY                                        | REPLACED                                                                                |
|---------------------------------------------+-----------------------------------------------------------------------------------------|
| Hellooo World                               | *H*e*l*l*o*o*o* *W*o*r*l*d*                                                             |
| How are you doing today?                    | *H*o*w* *a*r*e* *y*o*u* *d*o*i*n*g* *t*o*d*a*y*?*                                       |
| the quick brown fox jumps over the lazy dog | *t*h*e* *q*u*i*c*k* *b*r*o*w*n* *f*o*x* *j*u*m*p*s* *o*v*e*r* *t*h*e* *l*a*z*y* *d*o*g* |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | *P*A*C*K* *M*Y* *B*O*X* *W*I*T*H* *F*I*V*E* *D*O*Z*E*N* *L*I*Q*U*O*R* *J*U*G*S*         |
+---------------------------------------------+-----------------------------------------------------------------------------------------+

次の例では、順序や大文字小文字に関係なく、すべての母音を空白に置き換えて、削除します。

SELECT body,
       REGEXP_REPLACE(body, '[aeiou]', '', 1, 0, 'i') AS replaced
  FROM regexp_replace_demo;

+---------------------------------------------+----------------------------------+
| BODY                                        | REPLACED                         |
|---------------------------------------------+----------------------------------|
| Hellooo World                               | Hll Wrld                         |
| How are you doing today?                    | Hw r y dng tdy?                  |
| the quick brown fox jumps over the lazy dog | th qck brwn fx jmps vr th lzy dg |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | PCK MY BX WTH FV DZN LQR JGS     |
+---------------------------------------------+----------------------------------+

次の例では、単語の境界（\b）、その後に0個以上の単語文字（\S）、文字``o``および次の単語の境界までの0個以上の単語文字の一致によって、小文字の:code:`o`を含むすべての単語が対象から削除されます。

SELECT body,
       REGEXP_REPLACE(body, '\\b(\\S*)o(\\S*)\\b') AS replaced
  FROM regexp_replace_demo;

+---------------------------------------------+-----------------------------------------+
| BODY                                        | REPLACED                                |
|---------------------------------------------+-----------------------------------------|
| Hellooo World                               |                                         |
| How are you doing today?                    |  are   ?                                |
| the quick brown fox jumps over the lazy dog | the quick   jumps  the lazy             |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS |
+---------------------------------------------+-----------------------------------------+

次の例では、最初の``o``の前後の文字の交換、:code:`o`の文字シーケンス:code:`@@`への置き換えによって、小文字の:code:`o`を含むすべての単語を置き換えます。

SELECT body,
       REGEXP_REPLACE(body, '\\b(\\S*)o(\\S*)\\b', '\\2@@\\1') AS replaced
  FROM regexp_replace_demo;

+---------------------------------------------+-------------------------------------------------+
| BODY                                        | REPLACED                                        |
|---------------------------------------------+-------------------------------------------------|
| Hellooo World                               | @@Helloo rld@@W                                 |
| How are you doing today?                    | w@@H are u@@y ing@@d day@@t?                    |
| the quick brown fox jumps over the lazy dog | the quick wn@@br x@@f jumps ver@@ the lazy g@@d |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS         |
+---------------------------------------------+-------------------------------------------------+

次の例は前の例と同じですが、置換は対象の位置``3``で開始します。

SELECT body,
       REGEXP_REPLACE(body, '\\b(\\S*)o(\\S*)\\b', '\\2@@\\1', 3) AS replaced
  FROM regexp_replace_demo;

+---------------------------------------------+-------------------------------------------------+
| BODY                                        | REPLACED                                        |
|---------------------------------------------+-------------------------------------------------|
| Hellooo World                               | He@@lloo rld@@W                                 |
| How are you doing today?                    | How are u@@y ing@@d day@@t?                     |
| the quick brown fox jumps over the lazy dog | the quick wn@@br x@@f jumps ver@@ the lazy g@@d |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS         |
+---------------------------------------------+-------------------------------------------------+

次の例は前の例と同じですが、対象の位置``3``から始まり、3番目の出現のみが置換されています。

SELECT body,
       REGEXP_REPLACE(body, '\\b(\\S*)o(\\S*)\\b', '\\2@@\\1', 3, 3) AS replaced
  FROM regexp_replace_demo;

+---------------------------------------------+----------------------------------------------+
| BODY                                        | REPLACED                                     |
|---------------------------------------------+----------------------------------------------|
| Hellooo World                               | Hellooo World                                |
| How are you doing today?                    | How are you doing day@@t?                    |
| the quick brown fox jumps over the lazy dog | the quick brown fox jumps ver@@ the lazy dog |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS      |
+---------------------------------------------+----------------------------------------------+

次の例は前の例と同じですが、大文字と小文字を区別しないマッチングを使用しています。

SELECT body,
       REGEXP_REPLACE(body, '\\b(\\S*)o(\\S*)\\b', '\\2@@\\1', 3, 3, 'i') AS replaced
  FROM regexp_replace_demo;

+---------------------------------------------+----------------------------------------------+
| BODY                                        | REPLACED                                     |
|---------------------------------------------+----------------------------------------------|
| Hellooo World                               | Hellooo World                                |
| How are you doing today?                    | How are you doing day@@t?                    |
| the quick brown fox jumps over the lazy dog | the quick brown fox jumps ver@@ the lazy dog |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | PACK MY BOX WITH FIVE DOZEN R@@LIQU JUGS     |
+---------------------------------------------+----------------------------------------------+