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

REGEXP_SUBSTR_ALL¶

返回 ARRAY，其中包含字符串中与正则表达式匹配的所有子字符串。

别名：: REGEXP_EXTRACT_ALL

REGEXP_SUBSTR

语法¶

REGEXP_SUBSTR_ALL( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <regex_parameters> [ , <group_num> ] ] ] ] )

Copy

实参¶

必填：

subject

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

pattern

要匹配的模式。

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

可选：

position

函数开始搜索匹配项时，字符串开头的字符数。

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

occurrence

指定要从中开始返回匹配项的模式的第一次出现。

该函数会跳过第一个 occurrence - 1 匹配项。例如，如果存在 5 个匹配项，并且您为 occurrence 实参指定 3 个匹配项，则该函数将忽略前两个匹配项，并返回第三个、第四个和第五个匹配项。

默认：1

regex_parameters

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

参数	描述
`c`	区分大小写的匹配
`i`	不区分大小写的匹配
`m`	多行模式
`e`	提取子匹配项
`s`	POSIX 通配符 `.` 与 `\n` 匹配

默认：c

有关更多详细信息，请参阅为正则表达式指定参数。

备注

默认情况下，REGEXP_SUBSTR_ALL 会返回主题的整个匹配部分。但是，如果指定了 e 参数，则 REGEXP_SUBSTR_ALL 会返回字符串中与模式中第一组的匹配部分。如果指定了 e，但未同时指定 group_num，则 group_num 的值将默认为 1（第一组）。如果模式中没有子表达式，则 REGEXP_SUBSTR_ALL 表现得如同未设置 e。有关使用 e 的示例，请参阅本主题中的示例。

group_num

指定要提取的组。通过在正则表达式中使用括号来指定组。

如果指定了 group_num，则 Snowflake 允许在未同时指定 'e' 选项的情况下执行提取。隐含了 'e'。

Snowflake 至多支持 1024 个组。

有关使用 group_num 的示例，请参阅本主题中的示例。

返回¶

该函数返回 ARRAY 类型的值。该数组包含每个匹配子字符串的元素。

如果找不到匹配项，该函数会返回空数组。

在以下情况下，该函数会返回 NULL：

任意实参为 NULL。
您指定了 group_num，但模式没有指定与该数字相关的分组。例如，如果模式只指定一个组（例如 a(b)c），并且您使用 2 作为 group_num，则函数返回 NULL。

使用说明¶

有关使用正则表达式的其他信息，请参阅字符串函数（正则表达式）。

排序规则详细信息¶

Arguments with collation specifications currently aren't supported.

示例¶

以下示例中的模式匹配小写字母“a”，后跟数字。该示例返回包含所有匹配项的 ARRAY：

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]') AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a1", |
|   "a2", |
|   "a3", |
|   "a4", |
|   "a6"  |
| ]       |
+---------+

以下示例从字符串 (2) 中的第二个字符开始查找匹配项：

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]', 2) AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a2", |
|   "a3", |
|   "a4", |
|   "a6"  |
| ]       |
+---------+

以下示例从字符串 (3) 中模式的第三次匹配开始返回匹配项：

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]', 1, 3) AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a3", |
|   "a4", |
|   "a6"  |
| ]       |
+---------+

以下示例执行不区分大小写的匹配 (i)：

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]', 1, 1, 'i') AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a1", |
|   "a2", |
|   "a3", |
|   "a4", |
|   "A5", |
|   "a6"  |
| ]       |
+---------+

以下示例执行不区分大小写的匹配，并返回与第一组 (ie) 匹配的字符串部分：

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', '(a)([[:digit:]])', 1, 1, 'ie') AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a",  |
|   "a",  |
|   "a",  |
|   "a",  |
|   "A",  |
|   "a"   |
| ]       |
+---------+

以下示例演示了在未找到匹配项时该函数返回空数组：

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'b') AS matches;

Copy

+---------+
| MATCHES |
|---------|
| []      |
+---------+

此示例展示如何从双单词模式（第一个单词为 A）的第一个、第二个和第三个匹配项中检索字符串中的每个第二个单词的位置。

首先，创建表并插入数据：

CREATE OR REPLACE TABLE test_regexp_substr_all (string1 VARCHAR);;
INSERT INTO test_regexp_substr_all (string1) VALUES ('A MAN A PLAN A CANAL');

Copy

运行查询：

SELECT REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w+)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w+)', 1, 2, 'e', 1) AS result2,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w+)', 1, 3, 'e', 1) AS result3
  FROM test_regexp_substr_all;

Copy

+-----------+-----------+-----------+
| RESULT1   | RESULT2   | RESULT3   |
|-----------+-----------+-----------|
| [         | [         | [         |
|   "MAN",  |   "PLAN", |   "CANAL" |
|   "PLAN", |   "CANAL" | ]         |
|   "CANAL" | ]         |           |
| ]         |           |           |
+-----------+-----------+-----------+

此示例展示了如何检索字符串中每次出现的模式中的第一、第二和第三组。在这种情况下，返回值是每组中每个匹配单词的每个单独字母。

SELECT REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 2) AS result2,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 3) AS result3
  FROM test_regexp_substr_all;

Copy

+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 |
|---------+---------+---------|
| [       | [       | [       |
|   "M",  |   "A",  |   "N",  |
|   "P",  |   "L",  |   "A",  |
|   "C"   |   "A"   |   "N"   |
| ]       | ]       | ]       |
+---------+---------+---------+