字符串函数（正则表达式）

这些字符串函数执行与正则表达式（通常称为“regex”）匹配的操作。

本主题内容：

正则表达式函数列表

函数

备注

[ NOT ] REGEXP

RLIKE 的别名。

REGEXP_COUNT

REGEXP_EXTRACT_ALL

REGEXP_SUBSTR_ALL 的别名。

REGEXP_INSTR

REGEXP_LIKE

RLIKE 的别名。

REGEXP_REPLACE

REGEXP_SUBSTR

REGEXP_SUBSTR_ALL

[ NOT ] RLIKE

一般使用说明

在这些注释中，“主体”是指作为操作对象的字符串，“模式”是指正则表达式：

  • 主体通常是变量列，而模式通常是常量，但这不是必要条件；正则表达式函数的每个实参都可以是常量或变量。

  • 模式支持完整的 POSIX ERE （扩展正则表达式）语法。有关详细信息，请参阅维基百科中的 POSIX 基础和扩展 (link removed) 部分。

  • 模式还支持以下 Perl 反斜杠序列：

    • \d：十进制数字 (0-9)。

    • \D：非十进制数字。

    • \s：空格字符。

    • \S：非空格字符。

    • \w：“字词”字符（a-z、A-Z、下划线 [“_”] 或十进制数字）。

    • \W：非字词字符。

    • \b：字词边界。

    • \B：非字词边界。

    有关详细信息，请参阅维基百科中的 字符分类 (link removed) 部分或 Perl 文档中的 反斜杠序列 (http://perldoc.perl.org/perlrecharclass.html#Backslash-sequences) 部分。

    备注

    在 单引号字符串常量 中，必须对反斜杠序列中的反斜杠字符进行转义。例如，要指定 \d，请使用 \\d。有关详细信息，请参阅 在以单引号括起的字符串常量中指定正则表达式 （本主题内容）。

    如果使用 美元符号对 ($$) （而非单引号）分隔字符串，则无需对反斜杠进行转义。

  • 默认情况下，POSIX 通配符 . （在模式中）的匹配项中不包括换行符 \n （在主体中）。

    若要同时匹配换行符，请在 pattern 实参中将 . 替换为 (.|\n)，或在 parameters 实参中使用 s 实参（如下所述）。

  • 所有正则表达式函数均支持 Unicode。单个 Unicode 字符始终计为一个字符（即 POSIX 元字符 . 恰好匹配一个 Unicode 字符），而不论该字符相应二进制表示形式的字节长度如何。此外，对于接受或返回主体偏移的函数，单个 Unicode 字符计为 1。

为正则表达式指定参数

大多数正则表达式函数都支持可选的 parameters 实参。parameters 实参是一个 VARCHAR 字符串，用于指定正则表达式函数的匹配行为。支持以下参数：

参数

描述

c

启用区分大小写的匹配。

i

启用不区分大小写的匹配。

m

启用多行模式（即元字符 ^ 和 $ 标记主体任意行的开头和结尾）。默认情况下，多行模式处于禁用状态（即 ^ 和 $ 标记整个主体的开头和结尾）。

e

提取子匹配项；仅适用于 REGEXP_INSTR、REGEXP_SUBSTR、REGEXP_SUBSTR_ALL 和这些函数的别名。

s

使 POSIX 通配符 . 与 \n 相匹配。通配符匹配默认处于禁用状态。

默认字符串为 c，它指定了：

  • 区分大小写的匹配。

  • 单行模式。

  • 没有子匹配提取，但 REGEXP_REPLACE 除外，它始终使用子匹配提取。

  • POSIX 通配符 . 不会匹配换行符 \n。

在指定多个参数时，输入的字符串不应带有空格或分隔符。例如，ims 在多行模式下指定使用 POSIX 通配符匹配、不区分大小写的匹配功能。

如果 parameters 字符串中同时包含 c 和 i，则字符串中最后出现的匹配项指示函数是执行区分大小写的匹配，还是不区分大小写的匹配。例如，ci 指定不区分大小写的匹配，因为 i 在字符串中最后出现。

下面的示例显示区分大小写和不区分大小写匹配的结果有何不同。REGEXP_COUNT 函数对于 snow 和 SNOW，区分大小写的匹配（c 参数，默认值）不返回任何匹配，对于不区分大小写的匹配（i 参数）返回一个匹配：

SELECT REGEXP_COUNT('snow', 'SNOW', 1, 'c') AS case_sensitive_matching,
       REGEXP_COUNT('snow', 'SNOW', 1, 'i') AS case_insensitive_matching;
Copy
+-------------------------+---------------------------+
| CASE_SENSITIVE_MATCHING | CASE_INSENSITIVE_MATCHING |
|-------------------------+---------------------------|
|                       0 |                         1 |
+-------------------------+---------------------------+

使用带 e 参数的 REGEXP_SUBSTR 函数查找单词 Release，后跟一个或多个非单词字符，后跟一个或多个数字，然后返回与数字匹配的子字符串：

SELECT REGEXP_SUBSTR('Release 24', 'Release\\W+(\\d+)', 1, 1, 'e') AS release_number;
Copy
+----------------+
| RELEASE_NUMBER |
|----------------|
| 24             |
+----------------+

有关使用参数的更多示例，请参阅 REGEXP_INSTR、REGEXP_LIKE、REGEXP_SUBSTR、REGEXP_SUBSTR_ALL 和 [ NOT ] RLIKE。

匹配元字符

在正则表达式中，某些字符被视为具有特定含义的元字符。例如：

  • . 是 与任意单个字符匹配的元字符 (link removed)。

  • * 是 量词 (link removed)，与其前一个元素的零个或多个实例匹配。例如，BA* 与 B、BA、BAA 等匹配。

  • ? 是量词，与其前一个元素的零个或一个实例匹配。

若要匹配实际字符（例如实际的句点、星号或问号），必须使用反斜杠（例如 \.、\*、\? 等）对元字符进行转义。

备注

如果在 以单引号括起的字符串常量 中使用正则表达式，则必须使用第二个反斜杠（例如 \\.、\\*、\\? 等）对反斜杠进行转义。有关详细信息，请参阅 在以单引号括起的字符串常量中指定正则表达式。

例如，假设您要在字符串中查找一个左括号 (()。指定此项的方法之一是使用反斜杠对模式中的字符进行转义（例如 \(）。

如果将模式指定为 以单引号括起的字符串常量，还必须 使用第二个反斜杠对该反斜杠进行转义。

以下模式可匹配括号内的字母数字字符序列（例如 (NY)）：

SELECT REGEXP_SUBSTR('Customers - (NY)','\\([[:alnum:]]+\\)') AS location;
Copy
+----------+
| LOCATION |
|----------|
| (NY)     |
+----------+

有关其他示例，请参阅 在以单引号括起的字符串常量中使用元字符的示例。

请注意，如果您使用 以美元符号括起的字符串常量，则不需要对反斜杠字符进行转义：

SELECT REGEXP_SUBSTR('Customers - (NY)',$$\([[:alnum:]]+\)$$) AS location;
Copy
+----------+
| LOCATION |
|----------|
| (NY)     |
+----------+

使用反向引用

Snowflake 不支持正则表达式模式中的反向引用（在形式语言理论中称为“方块”(square)）；但 REGEXP_REPLACE 函数的替换字符串支持反向引用。

指定空模式

在大多数正则表达式函数中，空模式（即 ''）不匹配任何内容，甚至不匹配空主体。

但 REGEXP_LIKE 及其别名 [ NOT ] REGEXP 和 [ NOT ] RLIKE 属于例外情况，其中的空模式会匹配空主体，因为模式隐式锚定在两端（即 '' 自动变为 '^$'）。

空组（即子表达式 ()）匹配字符之间的空格，包括主体的开头和结尾。

在以美元符号括起的字符串常量中指定正则表达式

如果使用字符串常量为函数指定正则表达式，可以使用 以美元符号括起的字符串常量，避免 对正则表达式中的反斜杠字符进行转义。（如果您使用 以单引号括起的字符串常量，则需要对反斜杠进行转义。）

以美元符号括起的字符串常量的内容始终按字面含义解释。

例如，在转义 元字符 时，仅需使用一个反斜杠：

SELECT w2
  FROM wildcards
  WHERE REGEXP_LIKE(w2, $$\?$$);
Copy

使用 反向引用 时，仅需使用单个反斜杠：

SELECT w2, REGEXP_REPLACE(w2, '(.old)', $$very \1$$)
  FROM wildcards
  ORDER BY w2;
Copy

在以单引号括起的字符串常量中指定正则表达式

如果在 以单引号括起的字符串常量 中使用正则表达式，则必须使用第二个反斜杠，对 反斜杠序列 中的任何反斜杠进行转义。

备注

若要避免在正则表达式中转义反斜杠，可以使用 以美元符号括起的字符串常量，而非以单引号括起的字符串常量。

例如：

在以单引号括起的字符串常量中使用元字符的示例

本示例使用反斜杠作为搜索问号 (?) 的正则表达式中转义序列的一部分。

创建一个表，并插入一行，该行的一列中包含反斜杠，另一列中包含一个问号：

CREATE OR REPLACE TABLE wildcards (w VARCHAR, w2 VARCHAR);
INSERT INTO wildcards (w, w2) VALUES ('\\', '?');
Copy

以下查询会搜索问号字面量。搜索使用正则表达式，问号是正则表达式中的元字符，因此搜索必须对问号进行转义，这样才能将问号视为字面量。由于反斜杠出现在字符串字面量中，因此反斜杠本身也必须转义：

SELECT w2
  FROM wildcards
  WHERE REGEXP_LIKE(w2, '\\?');
Copy
+----+
| W2 |
|----|
| ?  |
+----+

在以下查询中，可以更清晰地看到正则表达式由两个字符（反斜杠转义字符和问号）组成：

SELECT w2
  FROM wildcards
  WHERE REGEXP_LIKE(w2, '\\' || '?');
Copy
+----+
| W2 |
|----|
| ?  |
+----+

前面的示例需要用到额外的反斜杠，原因只是转义字符是 字符串字面量 的一部分。正则表达式本身不需要转义字符。以下 SELECT 语句不需要将字符串字面量解析为 SQL 命令字符串的一部分，因此不需要字符串字面量所需的额外转义字符：

SELECT w, w2, w || w2 AS escape_sequence, w2
  FROM wildcards
  WHERE REGEXP_LIKE(w2, w || w2);
Copy
+---+----+-----------------+----+
| W | W2 | ESCAPE_SEQUENCE | W2 |
|---+----+-----------------+----|
| \ | ?  | \?              | ?  |
+---+----+-----------------+----+

在以单引号括起的字符串常量中使用反向引用的示例

如果在字符串字面量中使用 反向引用 <label-regex_backreferences>`（例如 `1``），则必须对该反向引用中包含的反斜杠进行转义。例如，要在 REGEXP_REPLACE 的替换字符串字面量中指定反向引用 \1，请使用 \\1。

以下示例使用先前创建的表。SELECT 使用反向引用，将正则表达式 .old 的每一个匹配实例均替换为单词“very”后接匹配字符串的副本：

INSERT INTO wildcards (w, w2) VALUES (NULL, 'When I am cold, I am bold.');
Copy
SELECT w2, REGEXP_REPLACE(w2, '(.old)', 'very \\1')
  FROM wildcards
  ORDER BY w2;
Copy
+----------------------------+------------------------------------------+
| W2                         | REGEXP_REPLACE(W2, '(.OLD)', 'VERY \\1') |
|----------------------------+------------------------------------------|
| ?                          | ?                                        |
| When I am cold, I am bold. | When I am very cold, I am very bold.     |
+----------------------------+------------------------------------------+
语言: 中文