How to extract with name with Regex in BigQuery?

Question

I am trying to extract a user name (x) from a string that looks like this: "Request is from User 'x' using the 'y' app ..."

I would like to extract x.

I tried to do it in the following way:

SELECT *,
REGEXP_EXTRACT(message,r"(?<=User ')[^']*") AS user_id
FROM `dataset...`

But I got an error from BigQuery:

Cannot parse regular expression: invalid perl operator: (?<

Any ideas on how to do it?

Thank you in advance!

Answer 1

Try the following:

select REGEXP_EXTRACT("Request is from User 'fvaksman' using the 'y' app ...", r"[User\s'](\w+)[']")