REGEXP_EXTRACT_ALL

描述

REGEXP_EXTRACT_ALL 函数用于对给定字符串str执行正则表达式匹配，所有与指定 pattern 匹配的文本串当中的与第一个子模式匹配的部分。为了使函数返回表示模式匹配部分的字符串数组，该模式必须与输入字符串 str 的一部分完全匹配。如果没有匹配项，或模式不包含任何子模式，则返回空字符串。

需要注意的是，在处理字符集匹配时，应使用 Utf-8 标准字符类。这确保函数能够正确识别和处理来自不同语言的各种字符。

如果 'pattern' 参数不符合正则表达式，则抛出错误

支持的字符匹配种类 : https://github.com/google/re2/wiki/Syntax

语法

REGEXP_EXTRACT_ALL(<str>, <pattern>)

参数

参数	描述
`<str>`	该参数为 String 类型。表示要执行正则表达式匹配的输入字符串。可以是字面值字符串或包含字符串数据的表列引用。
`<pattern>`	该参数也为 String 类型。指定用于与输入字符串匹配的正则表达式模式。该模式可以包含各种正则表达式构造，如字符类、量词和子模式。

返回值

函数返回表示输入字符串中与指定正则表达式的第一个子模式匹配部分的字符串数组。返回类型为 String 值数组。如果未找到匹配项，或模式没有子模式，则返回空数组。

默认行为：

默认配置	行为说明
`.` 匹配换行符	`.` 默认可以匹配 `\n`（换行符）。
大小写敏感	匹配时区分大小写。
`^`/`$` 匹配整个字符串边界	`^` 仅匹配字符串开头，`$` 仅匹配字符串结尾，而非每行的行首/行尾。
量词贪婪	`*`、`+` 等量词默认尽可能多地匹配。
UTF-8	字符串按 UTF-8 处理。

模式修饰符：

可通过在 pattern 前缀写入 (?flags) 来覆盖默认行为。多个修饰符可组合，如 (?im)；- 前缀表示关闭对应选项，如 (?-s)。

标志	含义
`(?i)`	大小写不敏感匹配
`(?-i)`	大小写敏感（默认）
`(?s)`	`.` 匹配换行符（默认已开启）
`(?-s)`	`.` 不匹配换行符
`(?m)`	多行模式：`^` 匹配每行行首，`$` 匹配每行行尾
`(?-m)`	单行模式：`^`/`$` 匹配整个字符串首尾（默认）
`(?U)`	量词非贪婪：`*`、`+` 等尽可能少地匹配
`(?-U)`	量词贪婪（默认）：`*`、`+` 等尽可能多地匹配

例子

围绕 'C' 的小写字母基本匹配,在这个示例中，模式([[:lower:]]+)C([[:lower:]]+)匹配字符串中一个或多个小写字母后跟 'C' 再跟一个或多个小写字母的部分。'C' 之前的第一个子模式([[:lower:]]+)匹配 'b'，因此结果为['b']。

mysql> SELECT regexp_extract_all('AbCdE', '([[:lower:]]+)C([[:lower:]]+)');
+--------------------------------------------------------------+
| regexp_extract_all('AbCdE', '([[:lower:]]+)C([[:lower:]]+)') |
+--------------------------------------------------------------+
| ['b']                                                        |
+--------------------------------------------------------------+

字符串中的多个匹配项,在这里，模式在字符串中匹配两个部分。第一个匹配的第一个子模式匹配 'b'，第二个匹配的第一个子模式匹配 'f'。因此结果为['b', 'f']。

mysql> SELECT regexp_extract_all('AbCdEfCg', '([[:lower:]]+)C([[:lower:]]+)');
+-----------------------------------------------------------------+
| regexp_extract_all('AbCdEfCg', '([[:lower:]]+)C([[:lower:]]+)') |
+-----------------------------------------------------------------+
| ['b','f']                                                       |
+-----------------------------------------------------------------+

从键值对中提取键, 该模式匹配字符串中的键值对。第一个子模式捕获键，因此结果为键的数组['abc', 'def', 'ghi']。

mysql> SELECT regexp_extract_all('abc=111, def=222, ghi=333','("[^"]+"|\\w+)=("[^"]+"|\\w+)');
+--------------------------------------------------------------------------------+
| regexp_extract_all('abc=111, def=222, ghi=333', '("[^"]+"|\w+)=("[^"]+"|\w+)') |
+--------------------------------------------------------------------------------+
| ['abc','def','ghi']                                                            |
+--------------------------------------------------------------------------------+

匹配汉字, 模式(\p{Han}+)(.+)首先通过第一个子模式(\p{Han}+)匹配一个或多个汉字，因此结果为['这是一段中文']。

mysql> select regexp_extract_all('这是一段中文 This is a passage in English 1234567', '(\\p{Han}+)(.+)');
+------------------------------------------------------------------------------------------------+
| regexp_extract_all('这是一段中文 This is a passage in English 1234567', '(\p{Han}+)(.+)')       |
+------------------------------------------------------------------------------------------------+
| ['这是一段中文']                                                                               |
+------------------------------------------------------------------------------------------------+

插入数据并使用 REGEXP_EXTRACT_ALL

CREATE TABLE test_regexp_extract_all (
    id INT,
    text_content VARCHAR(255),
    pattern VARCHAR(255)
) PROPERTIES ("replication_num"="1");


INSERT INTO test_regexp_extract_all VALUES
(1, 'apple1, banana2, cherry3', '([a-zA-Z]+)\\d'),
(2, 'red#123, blue#456, green#789', '([a-zA-Z]+)#\\d+'),
(3, 'hello@example.com, world@test.net', '([a-zA-Z]+)@');


SELECT id, regexp_extract_all(text_content, pattern) AS extracted_data
FROM test_regexp_extract_all;

+------+----------------------+
| id   | extracted_data       |
+------+----------------------+
|    1 | ['apple', 'banana', 'cherry'] |
|    2 | ['red', 'blue', 'green']     |
|    3 | ['hello', 'world']           |
+------+----------------------+

没有匹配到，返回空字符串

SELECT REGEXP_EXTRACT_ALL('ABC', '(\\d+)');

+-------------------------------------+
| REGEXP_EXTRACT_ALL('ABC', '(\\d+)') |
+-------------------------------------+
|                                     |
+-------------------------------------+

emoji字符匹配

mysql> SELECT REGEXP_EXTRACT_ALL('👩‍💻,👨‍🚀', '(💻|🚀)');
+--------------------------------------------------------------+
| REGEXP_EXTRACT_ALL('👩‍💻,👨‍🚀', '(💻|🚀)')                     |
+--------------------------------------------------------------+
| ['💻','🚀']                                                  |
+--------------------------------------------------------------+

'Str' 是 NULL,返回 NULL

SELECT regexp_extract_all(NULL, '([a-z]+)');

+--------------------------------------+
| regexp_extract_all(NULL, '([a-z]+)') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

'pattern' 是 NULL,返回 NULL

SELECT regexp_extract_all('Hello World', NULL);

+-----------------------------------------+
| regexp_extract_all('Hello World', NULL) |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+

全部参数都是 NULL，返回 NULL

SELECT regexp_extract_all(NULL,NULL);

+-------------------------------+
| regexp_extract_all(NULL,NULL) |
+-------------------------------+
| NULL                          |
+--------

如果 'pattern' 参数不符合正则表达式，则抛出错误

SELECT regexp_extract_all('hello (world) 123', '([[:alpha:]+');

ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.2)[INVALID_ARGUMENT]Could not compile regexp pattern: ([[:alpha:]+
Error: missing ]: [[:alpha:]+

模式修饰符

大小写不敏感：(?i) 使匹配忽略大小写

SELECT REGEXP_EXTRACT_ALL('Hello hello HELLO', '(hello)') AS case_sensitive,
       REGEXP_EXTRACT_ALL('Hello hello HELLO', '(?i)(hello)') AS case_insensitive;

+----------------+---------------------------+
| case_sensitive | case_insensitive          |
+----------------+---------------------------+
| ['hello']      | ['Hello','hello','HELLO'] |
+----------------+---------------------------+

多行模式：(?m) 使 ^ 和 $ 匹配每行行首/行尾

SELECT REGEXP_EXTRACT_ALL('foo\nbar\nbaz', '^([a-z]+)') AS single_line,
       REGEXP_EXTRACT_ALL('foo\nbar\nbaz', '(?m)^([a-z]+)') AS multi_line;

+-------------+---------------------+
| single_line | multi_line          |
+-------------+---------------------+
| ['foo']     | ['foo','bar','baz'] |
+-------------+---------------------+

贪婪与非贪婪：(?U) 使量词尽可能少地匹配

SELECT REGEXP_EXTRACT_ALL('aXbXcXd', '(a.*X)') AS greedy,
       REGEXP_EXTRACT_ALL('aXbXcXd', '(?U)(a.*X)') AS non_greedy;

+----------+------------+
| greedy   | non_greedy |
+----------+------------+
| ['aXbXcX'] | ['aX']   |
+----------+------------+

描述​

语法​

参数​

返回值​

例子​

描述

语法

参数

返回值

例子