How to extract with name with Regex in BigQuery?-CodePudding

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!

CodePudding user response：

Try the following:

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

CodePudding user response：

You need to use a capturing group rather than a positive lookbehind:

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

Here,

User ' - matches a User ' string
([^']*) - captures zero or more chars other than ' into Group 1 that is returned by the REGEXP_EXTRACT function.