CODEWITHSUNDEEP
sqlregexgoogle-bigquery
Feliks Vaksman
Feliks Vaksman

Reputation: 71

How to extract with name with Regex in BigQuery?

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!

Upvotes: 1

Views: 1477

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627126

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.

Upvotes: 2

Daniel Zagales
Daniel Zagales

Reputation: 3034

Try the following:

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

Upvotes: 0

Related Questions