I'm trying to write SQL in big query to replace strings below with middle part of the string
REGEXP_REPLACE('sports.xxxx.com/en-gb/betting/football', '.*', '(?<=\b\/betting\/)(\w+)')
Error: Invalid string literal: '(?<=\b\/betting\/)(\w+)'
The error is due to the single backslashes you used in the string literal that formed invalid escape seuqences.
You need a capturing group with
SELECT REGEXP_EXTRACT('sports.ladbrokes.com/en-gb/betting/football', r'\b/betting/(\w+)')
See the regex demo
Note also that Big Query regex syntex is powered by the RE2 regex library that does not support lookarounds at all (and you tried to use a positive loobehind
The pattern details:
\b- a word boundary (trailing). It might be unnecessary, you may remove it safely
/betting/- a literal substring
/betting/(note the forward slashes are not special for regex, they need no escaping)
(\w+)- Group 1, the part of the string that will be returned by the
REGEX_EXTRACT, capturing 1 or more word chars (letters, digits or underscores).