CODEWITHSUNDEEP
regexgoogle-bigquery
Chris Morgan
Chris Morgan

Reputation: 21

REGEX_EXTRACT_ALL Big Query

I have written a regex which works in Regex101, however does not work in BigQuery, I need to figure out a translation to run it in BQ.

This is the regex: (?<=\()(.*?)(?=\;)
This is the data: (400.0; 10/26/2020; 12/13/2020;); (500.0; 12/21/2020; 01/31/2021;);

This pulls out 400.0 and 500.0.

However I receive an error

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

I believe it is due to the lookbehind, could anyone help?

Upvotes: 0

Views: 686

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626845

RE2 regex library does not support lookarounds.

In this case, you may actually remove the lookaround syntax and use

\((.*?);

Alternatively,

\(([^;]*);
\(([0-9]+(?:\.[0-9]+)?);

That is, you need to use a regex with a capturing group that matches the text you need to extract.

See the regex demo.

Details:

  • \((.*?); - a ( char, then any zero or more chars other than line break chars, as few as possible, captured into Group 1, and then a ; char
  • \(([^;]*); - a (, then any zero or more chars other than ; captured into Group 1, and then a ;
  • \(([0-9]+(?:\.[0-9]+)?); - a ( char, then one or more digits followed with an optional occurrence of a . and one or more digits captured into Group 1 and then a ; char.

Upvotes: 1

Related Questions