How to do both positive lookahead and lookbehind in Bigquery

user_3011380 0
Viewed 71

We want to match two specific characters around another word, for example, to match - or _ around word ben,

abentest    - not match
bentest     - not match
aben        - not match
a-ben       - match
a_ben       - match
ben-test    - match
a-ben-test  - match
a_ben-test  - match

It could be done through positive lookahead and lookbeind like .*((?<=[_-])(ben)|(ben)(?=[_-])).*.

However, the lookahead and lookbehind do not support Bigquery regex re2. What is the alternative way to do it in Bigquery?

re2 google-bigquery sql regex
1 Answers

You could phrase this sans lookarounds by using a regex alternation:

.*(?:[_-]ben|ben[_-]).*

Here is a working regex demo.

user_18632290
Related