Extract Substring using Regular expression aka Regex

Question

I have Snowflake POSIX BRE engine and wants to extract below substring from given text. POSIX doesn't support lookbehind and lookahead. Please help with regex expression.

Ex 1: 2022 CKL04 TER-PRO:CPT-REFRESH PRD|NPR Substring Needed: CPT-REFRESH PRD

Ex 2 : 2022 CA4A TER-PRO:CPT-REFRESH PRD Substring Needed: CPT-REFRESH PRD

Ex 3 : 2022 CDDR4A TER-PRO:CPT-LEASING PRD|MC|LQPRI13 Substring Needed: CPT-LEASING PRD

Ex 4 : 2022 CAP04A TER-PRO:PRODUCT|NPR Substring Needed: PRODUCT

Ex 5 : 2022 CS040 TER-PRO:MS-PRD & SVC ANNUAL|NPR Substring Needed: MS-PRD & SVC ANNUAL

I need all of the characters after : and before first | or end of the string if no | is available.

I am able to extract with lookahead and lookbehind using this regex (?<=:).+?(?=||$) but I need solution for POSIX BRE without lookahead and lookbehind

would [\w\-\s]+ work if then you just grab the second match? — depperm
– depperm, Commented Jan 25, 2023 at 17:05
Thanks. I added few more examples where it isn't working and match number also varies for different examples. Its showing also this error in Snowflake "Invalid regular expression: '[w-s]+', invalid character class range: w-s". — Ombir Rathee
– Ombir Rathee, Commented Jan 25, 2023 at 17:34
You can use lookahead and lookbehind regex in Snowflake using a JavaScript UDF. github.com/GregPavlik/SnowflakeUDFs/tree/main/…. You could also use instr and substr to extract the text without using regular expressions. — Greg Pavlik
– Greg Pavlik, Commented Jan 25, 2023 at 17:36
the regex I provided was not a range w-s it was \w, \-, and \s, for example 5 you'd need to add &, and only works if you can choose the second match — depperm
– depperm, Commented Jan 25, 2023 at 17:37

Rajat · Accepted Answer · 2023-01-25 18:30:29Z

2

This should work. Logic is to start at : then keep capturing everything after that unless we encounter a |, at which point we just stop there. I am using a capture group so we don't include the preceding :

select regexp_substr(col,':([^|]+)',1,1,'e');

answered Jan 25, 2023 at 18:30

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ombir Rathee Over a year ago

Thank you. Its works as I expected.

Collectives™ on Stack Overflow

Extract Substring using Regular expression aka Regex

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related