REGEXP_REPLACE

将所有与正则表达式匹配的子字符串实例替换为另一个子字符串。REGEXP_REPLACE 与 REPLACE 函数相似，不同的是，它使用正则表达式来选择要替换的子字符串。

此函数使用默认区域设置对 UTF-8 字符串进行操作，即使区域设置设置为其他方式。

重要

如果要从 Oracle 数据库转移正则表达式查询，请记住 Oracle 将零长度字符串视为等同于 NULL，而 Vertica 却不会这样。

语法

REGEXP_REPLACE ( string-expression, target 
   [, replacement [, position [, occurrence[...] [, regexp‑modifier]]]] )

参数

string-expression

VARCHAR 或 LONG VARCHAR 表达式，用于评估在模式中指定的正则表达式是否匹配。如果字符串表达式位于在 Flex 表或列式表的 __raw__ 列中，则在搜索模式之前将字符串转换为 LONG VARCHAR。

模式

与字符串表达式匹配的正则表达式。正则表达式必须符合 Perl 正则表达式语法。

replacement

用于替换匹配的子字符串的字符串。如果不提供 replacement，函数将会删除匹配的子字符串。替换字符串可能包含正则表达式所捕获的子字符串的 baccalaureate。第一个捕获的子字符串将会使用 \1 插入到替换字符串，第二个则会使用 \2 插入到替换字符串，依此类推。

position

函数应该从自字符串开头算起的多少个字符开始搜索匹配项。默认情况下，函数从第一个（最左侧）字符开始搜索匹配项。如果将此参数设置为大于 1 的值，将从您指定的第 n 个字符开始搜索匹配项。

默认值： 1

occurrence

控制要返回字符串中第几次出现的模式匹配项。默认情况下，函数返回第一个匹配子字符串的位置。使用此参数可以查找后续匹配子字符串的位置。例如，如果将此参数设置为 3，将返回与模式匹配的第三个子字符串的位置。

默认值： 1

regexp‑modifier

[可选] 一个或多个单字符标志，可修改正则表达式模式与字符串表达式匹配的方式：

b：将字符串视为二进制八位字节数，而不是 UTF-8 字符。
c （默认值）：强制匹配区分大小写。
i：强制匹配不区分大小写。
m：将要匹配的字符串视为多行。使用此修饰符，可使行头 (^) 和行尾 ($) 的正则表达式运算符与字符串内的换行符 (\n) 相匹配。如果没有 m 修饰符，行头和行尾运算符仅与字符串的开头和结尾相匹配。
n：将正则表达式运算符 (.) 与换行符 (\n) 相匹配。默认情况下，. 运算符可与除换行符以外的任何字符相匹配。
x：将注释添加到正则表达式。x 修饰符可导致函数忽略正则表达式中所有未经转义的空格字符和注释。注释以哈希 (#) 字符开始，以换行符 (\n) 结束。在字符串中匹配的正则表达式中的所有空格都必须使用反斜杠 (\\) 字符进行转义。

Oracle 如何处理子表达式

与 Oracle 不同，Vertica 可以处理捕获到的无限数量的子表达式，而 Oracle 则限制为 9 个。

在 Vertica 中，可以在替换模式中使用 \10 来访问正则表达式中第 10 对圆括号所捕获的子字符串。在 Oracle 中，\10 将作为第一对圆括号所捕获的子字符串进行处理，后面紧跟 0。要在 Vertica 强制使用此 Oracle 行为，请使用 \g 反向引用，并将捕获的子表达式编号括在花括号内。例如，\g{1}0 表示第一对圆括号所捕获的子字符串，后面紧跟 0。

也可以对捕获的子表达式进行命名，以增强正则表达式的明确性。有关详细信息，请参阅 PCRE 文档。

示例

在字符串 healthy, wealthy, and wise 中查找以 thy 结尾的“字符元”组（字母、数字和下划线），并将其替换为空字符串。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy');
 REGEXP_REPLACE
----------------
 , , and wise
(1 row)

查找以 thy 结尾的字符元组，并替换为字符串 something。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy', 'something');
         REGEXP_REPLACE
--------------------------------
 something, something, and wise
(1 row)

查找以 thy 结尾的字符元组，并从字符串中的第三个字符开始替换为字符串 something。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy', 'something', 3);
          REGEXP_REPLACE
----------------------------------
 hesomething, something, and wise
(1 row)

将以 thy 结尾的第二个字符元组替换为字符串 something。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy', 'something', 1, 2);
        REGEXP_REPLACE
------------------------------
 healthy, something, and wise
(1 row)

查找以 thy 结尾的字符元组（捕获 thy 前面的字母），并替换为捕获到的字母加上字母 ish。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','(\w+)thy', '\1ish');
       REGEXP_REPLACE
----------------------------
 healish, wealish, and wise
(1 row)

创建表以演示替换查询中的字符串。

=> CREATE TABLE customers (name varchar(50), phone varchar(11));
CREATE TABLE
=> CREATE PROJECTION customers1 AS SELECT * FROM customers;
CREATE PROJECTION
=> COPY customers FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> Able, Adam|17815551234
>> Baker,Bob|18005551111
>> Chu,Cindy|16175559876
>> Dodd,Dinara|15083452121
>> \.

查询客户，使用 REGEXP_REPLACE 设置电话号码格式。

=> SELECT name, REGEXP_REPLACE(phone, '(\d)(\d{3})(\d{3})(\d{4})',
'\1-(\2) \3-\4') as phone FROM customers;
    name     |      phone
-------------+------------------
 Able, Adam  | 1-(781) 555-1234
 Baker,Bob   | 1-(800) 555-1111
 Chu,Cindy   | 1-(617) 555-9876
 Dodd,Dinara | 1-(508) 345-2121
(4 rows)