类别：: 字符串函数（正则表达式）

REGEXP_REPLACE¶

Returns the subject with the specified pattern --- or all occurrences of the pattern --- either removed or replaced by a replacement string.

语法¶

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

Copy

实参¶

必填：

subject

用于搜索匹配项的字符串。

pattern

要匹配的模式。

有关指定模式的准则，请参阅字符串函数（正则表达式）。

可选：

replacement

替换与模式匹配的子字符串的字符串。如果指定了空字符串，则该函数将删除所有匹配的模式并返回生成的字符串。

默认值：'' （空字符串）。

position

Number of characters from the beginning of the string where the function starts searching for matches. The value must be a positive integer.

默认值：``1``（搜索匹配项时，从左边的第一个字符开始）

occurrence

指定要替换的模式的匹配项。如果指定 0，则替换所有匹配项。

默认值：0 （所有匹配项）

parameters

包含一个或多个字符的字符串，指定用于搜索匹配项的参数。支持的值：

参数	描述
`c`	区分大小写的匹配
`i`	不区分大小写的匹配
`m`	多行模式
`e`	提取子匹配项
`s`	Single-line mode POSIX wildcard character `.` matches `\n`

默认：c

For more information, see 为正则表达式指定参数.

返回¶

返回类型 VARCHAR 的值。

如果未找到匹配项，则返回原始主体。

如果任何实参为 NULL，则返回 NULL。

使用说明¶

The replacement string can contain backreferences to capture groups; for example, sub-expressions of the pattern. A capture group is a regular expression that is enclosed within parentheses (( )). The maximum number of capture groups is nine.

反向引用与捕获组内的表达式匹配。反向引用的形式为 n，其中 n 是 0 到 9 之间的值（含 0 和 9），它指的是捕获组的匹配实例。有关更多信息，请参阅 `示例`_（本主题内容）。

目前，括号 (( )) 和方括号 ([ ]) 必须进行双重转义，才能将它们解析为字面量字符串。

以下示例显示了如何移除括号：

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

Copy

+----------------+
| 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;

Copy

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

以下示例匹配字符串 times 并将其替换为字符串 days。匹配从字符串中的第一个字符开始，并替换子字符串的第二个匹配项：

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

Copy

+----------------------------------------------------+
| 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;

Copy

+--------------------------------+
| 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');

Copy

以下示例通过空分组 (()) 在主题字符串的每个字符间（包括首尾）插入字符 *，该分组能匹配任意两个字符之间的位置：

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

Copy

+---------------------------------------------+-----------------------------------------------------------------------------------------+
| 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;

Copy

+---------------------------------------------+----------------------------------+
| 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)，后接零个或多个单词字符 (\S)、字母 o，再接零个或多个单词字符直到下一个单词边界，从而移除主题中包含小写字母 o 的所有单词：

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

Copy

+---------------------------------------------+-----------------------------------------+
| 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 的单词：调换首个 o 实例前后的字母，并将 o 替换为字符序列 @@：

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

Copy

+---------------------------------------------+-------------------------------------------------+
| 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;

Copy

+---------------------------------------------+-------------------------------------------------+
| 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 位开始的第三次匹配项：

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

Copy

+---------------------------------------------+----------------------------------------------+
| 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;

Copy

+---------------------------------------------+----------------------------------------------+
| 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     |
+---------------------------------------------+----------------------------------------------+