在 SELECT 列表中调用 REGEXP_SUBSTR 函数
在 SELECT 列表中调用 REGEXP_SUBSTR 函数以提取或显示与模式匹配的值。
此示例查找单词 the 的第一次出现，后跟一个或多个非单词字符（例如，用于分隔单词的空格），后跟一个或多个单词字符。
“单词字符”不仅包括字母 a-z 和 A-Z，还包括下划线（“_”）和十进制数字 0-9，但不包括空格、标点符号等。
SELECT id,
REGEXP_SUBSTR(string1, 'the\\W+\\w+') AS result
FROM demo2
ORDER BY id;
+----+--------------+
| ID | RESULT |
|----+--------------|
| 2 | the best |
| 3 | the string |
| 4 | NULL |
+----+--------------+
从字符串的位置 1 开始，查找单词 the 的第二次出现，后跟一个或多个非单词字符，后跟一个或多个单词字符。
SELECT id,
REGEXP_SUBSTR(string1, 'the\\W+\\w+', 1, 2) AS result
FROM demo2
ORDER BY id;
+----+-------------+
| ID | RESULT |
|----+-------------|
| 2 | the worst |
| 3 | the extra |
| 4 | NULL |
+----+-------------+
从字符串的位置 1 开始，查找单词 the 的第二次出现，后跟一个或多个非单词字符，后跟一个或多个单词字符。
与其返回整个匹配项，不如只返回“组”（例如，子字符串中与括号中正则表达式部分匹配的部分）。在这种情况下，返回值应该是“the”之后的单词。
SELECT id,
REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2, 'e', 1) AS result
FROM demo2
ORDER BY id;
+----+--------+
| ID | RESULT |
|----+--------|
| 2 | worst |
| 3 | extra |
| 4 | NULL |
+----+--------+
此示例说明如何从其中第一个单词为 A 的双字模式的第一、第二和第三个匹配项中检索第二个单词。此示例也表明，如果尝试获取超出最后一个模式的词，Snowflake 会返回 NULL。
首先，创建表并插入数据：
CREATE OR REPLACE TABLE test_regexp_substr (string1 VARCHAR);;
INSERT INTO test_regexp_substr (string1) VALUES ('A MAN A PLAN A CANAL');
运行查询：
SELECT REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 1, 'e', 1) AS result1,
REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 2, 'e', 1) AS result2,
REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 3, 'e', 1) AS result3,
REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 4, 'e', 1) AS result4
FROM test_regexp_substr;
+---------+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 | RESULT4 |
|---------+---------+---------+---------|
| MAN | PLAN | CANAL | NULL |
+---------+---------+---------+---------+
此示例说明如何在模式首次出现时检索第一、第二和第三组。在这种情况下，返回值是单词 MAN 的各个字母。
SELECT REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 1) AS result1,
REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 2) AS result2,
REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 3) AS result3
FROM test_regexp_substr;
+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 |
|---------+---------+---------|
| M | A | N |
+---------+---------+---------+
以下是一些其他示例。
创建表并插入数据：
CREATE OR REPLACE TABLE message(body VARCHAR(255));
INSERT INTO message 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');
通过匹配单词边界 (\b) 返回包含小写字母 o 的第一个匹配项，后跟零个或多个单词字符 (\S)、字母 o，然后是零或多个单词字符，直到下一个单词边界为止：
SELECT body,
REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b') AS result
FROM message;
+---------------------------------------------+---------+
| BODY | RESULT |
|---------------------------------------------+---------|
| Hellooo World | Hellooo |
| How are you doing today? | How |
| the quick brown fox jumps over the lazy dog | brown |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS | NULL |
+---------------------------------------------+---------+
返回包含小写 o 的第一个匹配项，从主题中的第三个字符开始：
SELECT body,
REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b', 3) AS result
FROM message;
+---------------------------------------------+--------+
| BODY | RESULT |
|---------------------------------------------+--------|
| Hellooo World | llooo |
| How are you doing today? | you |
| the quick brown fox jumps over the lazy dog | brown |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS | NULL |
+---------------------------------------------+--------+
返回包含小写 o 的第三个匹配项，从主题中的第三个字符开始：
SELECT body,
REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b', 3, 3) AS result
FROM message;
+---------------------------------------------+--------+
| BODY | RESULT |
|---------------------------------------------+--------|
| Hellooo World | NULL |
| How are you doing today? | today |
| the quick brown fox jumps over the lazy dog | over |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS | NULL |
+---------------------------------------------+--------+
返回包含小写 o 的第三个匹配项，从主题中的第三个字符开始，不区分大小写的匹配：
SELECT body,
REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b', 3, 3, 'i') AS result
FROM message;
+---------------------------------------------+--------+
| BODY | RESULT |
|---------------------------------------------+--------|
| Hellooo World | NULL |
| How are you doing today? | today |
| the quick brown fox jumps over the lazy dog | over |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS | LIQUOR |
+---------------------------------------------+--------+
此示例说明，您可以通过指定空字符串来显式省略任何正则表达式参数。
SELECT body,
REGEXP_SUBSTR(body, '(H\\S*o\\S*\\b).*', 1, 1, '') AS result
FROM message;
+---------------------------------------------+--------------------------+
| BODY | RESULT |
|---------------------------------------------+--------------------------|
| Hellooo World | Hellooo World |
| How are you doing today? | How are you doing today? |
| the quick brown fox jumps over the lazy dog | NULL |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS | NULL |
+---------------------------------------------+--------------------------+
以下示例说明了重叠出现的情况。首先，创建表并插入数据：
CREATE OR REPLACE TABLE overlap (
id NUMBER,
a STRING);
INSERT INTO overlap VALUES (1, ',abc,def,ghi,jkl,');
INSERT INTO overlap VALUES (2, ',abc,,def,,ghi,,jkl,');
SELECT * FROM overlap;
+----+----------------------+
| ID | A |
|----+----------------------|
| 1 | ,abc,def,ghi,jkl, |
| 2 | ,abc,,def,,ghi,,jkl, |
+----+----------------------+
运行查询，在每行中查找以下模式的第二次出现：标点符号后是数字和字母，然后是标点符号。
SELECT id,
REGEXP_SUBSTR(a,'[[:punct:]][[:alnum:]]+[[:punct:]]', 1, 2) AS result
FROM overlap;
+----+--------+
| ID | RESULT |
|----+--------|
| 1 | ,ghi, |
| 2 | ,def, |
+----+--------+
以下示例说明如何使用模式匹配和连接从 Apache HTTP Server 访问日志中创建 JSON 对象。首先，创建表并插入数据：
CREATE OR REPLACE TABLE test_regexp_log (logs VARCHAR);
INSERT INTO test_regexp_log (logs) VALUES
('127.0.0.1 - - [10/Jan/2018:16:55:36 -0800] "GET / HTTP/1.0" 200 2216'),
('192.168.2.20 - - [14/Feb/2018:10:27:10 -0800] "GET /cgi-bin/try/ HTTP/1.0" 200 3395');
SELECT * from test_regexp_log
+-------------------------------------------------------------------------------------+
| LOGS |
|-------------------------------------------------------------------------------------|
| 127.0.0.1 - - [10/Jan/2018:16:55:36 -0800] "GET / HTTP/1.0" 200 2216 |
| 192.168.2.20 - - [14/Feb/2018:10:27:10 -0800] "GET /cgi-bin/try/ HTTP/1.0" 200 3395 |
+-------------------------------------------------------------------------------------+
运行查询：
SELECT '{ "ip_addr":"'
|| REGEXP_SUBSTR (logs,'\\b\\d{1,3}\.\\d{1,3}\.\\d{1,3}\.\\d{1,3}\\b')
|| '", "date":"'
|| REGEXP_SUBSTR (logs,'([\\w:\/]+\\s[+\-]\\d{4})')
|| '", "request":"'
|| REGEXP_SUBSTR (logs,'\"((\\S+) (\\S+) (\\S+))\"', 1, 1, 'e')
|| '", "status":"'
|| REGEXP_SUBSTR (logs,'(\\d{3}) \\d+', 1, 1, 'e')
|| '", "size":"'
|| REGEXP_SUBSTR (logs,'\\d{3} (\\d+)', 1, 1, 'e')
|| '"}' as Apache_HTTP_Server_Access
FROM test_regexp_log;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| APACHE_HTTP_SERVER_ACCESS |
|-----------------------------------------------------------------------------------------------------------------------------------------|
| { "ip_addr":"127.0.0.1", "date":"10/Jan/2018:16:55:36 -0800", "request":"GET / HTTP/1.0", "status":"200", "size":"2216"} |
| { "ip_addr":"192.168.2.20", "date":"14/Feb/2018:10:27:10 -0800", "request":"GET /cgi-bin/try/ HTTP/1.0", "status":"200", "size":"3395"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+