snowflake-cloud-data-platform - 使用正则表达式提取 Snowflake 中的特定文本

Question

想要获取键 [Work Order Description:] 的值并打印它。

使用的脚本:

CREATE TABLE demo3 (id INT, log VARCHAR);
INSERT INTO demo3 (id, log) VALUES 
(1, 'Work order submitted on 12-03-2020
Work Order Description:Lights are not working
Work order status:Completed'),
(2, 'Work order submitted on 5-04-2020
Work order Priority:P3
Work Order Description:Electrical equipment issue
Work order status:Completed');

实现的解决方案:从 demo3 中选择 id, substr(log, regexp_instr(log, 'Work Order Description:') + 23, 300) 作为日志；

实现的解决方案输出:

id log
1  Lights are not working Work order status:Completed
2  Electrical equipment issue Work order status:Completed

问题:最后一行也附加到输出。相反，我希望在行尾修剪输出。

所需的期望输出:

1, Lights are not working
2, Electrical equipment issue

任何帮助将不胜感激。提前致谢。

Answer 1

您可以像这样使用 REGEXP_SUBSTR():

WITH data AS (
SELECT 2 AS id, 'Work order submitted on 5-04-2020
Work order Priority:P3
Work Order Description:Electrical equipment issue
Work order status:Completed' AS log
)

SELECT id, REGEXP_SUBSTR(log, 'Work Order Description:(.*)', 1, 1, 'e')
FROM data