Negative Lookbehind Workaround For Posix Regex

user_12939362 0
Viewed 195

I need to exclude a string from being matched if it's preceeded by a certain character, and my regex engine is POSIX. I was able to get the desired result using a negative lookbehind on https://regexr.com/ but just discovered that won't work on my POSIX SnowFlake platform :-( .

I'm trying to standardize variations of company names and want to match the strings that end in 'COMPANY', 'CO', or 'CO.', but not match them if preceeded by an ' & '. So 'COMPANY' would get matched in 'POWERWASH COMPANY', but not in 'JONES & COMPANY'.

Is there a way I can accomplish this in POSIX regex? I was able to get this to work using a negative lookbehind as follows:

(?<!&)( COMPANY$| CO[.]?$)
snowflake-cloud-data-platform lookbehind regex-lookarounds posix regex
2 Answers

You may use a capturing group (as you're already doing) and put the irrelevant parts outside of the group:

[^&]( COMPANY| CO\.?)$

Demo.

I'm not that familiar with SnowFlake but according to the documentation, you can extract the value captured by group 1 using the regexp_substr method as follows:

regexp_substr(input, '[^&]( COMPANY| CO\.?)$', 1, 1, 'e', 1)
--                                                        ^
--                                              Group number

Note that [^&] will match any character other than '&'. If you'd like the match to succeed even if the target word is at the beginning of the string, you may use (^|[^&]) in place of [^&]. In that case, you may extract the value from group 2 rather than group 1.

user_89676120

You can use

(^|[^&])( COMPANY| CO[.]?)$

See the regex demo.

Whatever you capture is usually of no importance in POSIX regex, but in other cases it is usually easy to work around using additional capturing groups and code logic.

Regex details:

  • (^|[^&]) - start of string or any char other than &
  • ( COMPANY| CO[.]?) - either a space and COMPANY, or a space, CO, an optional . and
  • $ - end of string
user_38329700
Related