Using regex to extract particular text in Snowflake

Question

Would like to fetch the value for the key[Work Order Description:] and print it.

Scripts Used:

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');

Implemented Solution: select id, substr(log, regexp_instr(log, 'Work Order Description:') + 23, 300) as log from demo3;

Implemented Solution Output:

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

Issue: Last lines are also attaching to the output. Instead I am looking to trim the output at the end of the line.

Desired Output Required:

1, Lights are not working
2, Electrical equipment issue

Any help will be appreciated. Thanks in advance.

user_132438 · Answer

You can use REGEXP_SUBSTR() as in:

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

Using regex to extract particular text in Snowflake

1 Answers