Error: Big Query REGEX_EXTRACT Cannot parse regular expression: invalid perl operator: (?<

user_4188557 0
Viewed 9204

My attempt to extract the #2 from #1 in BigQuery

REGEXP_EXTRACT(",\"AQk8tmAg94ZUZwqYKd6kHrswiVZR0wKNuTvSpr6COCLpki\"", r"(?<=,\")[a-zA-Z0-9]*")

#1 String from which I am trying to extract

,"AQk8tmAg94ZUZwqYKd6kHrswiVZR0wKNuTvSpr6COCLpki"

#2 Expected result of the REGEX_EXTRACT

AQk8tmAg94ZUZwqYKd6kHrswiVZR0wKNuTvSpr6COCLpki

Can someone please help me in the Regex to correctly escape quotes and brackets to extract #2 from #1?

google-bigquery regex
2 Answers

I suspect the error has to do with your lookbehind, which REGEX_EXTRACT does not support. One workaround might be to use REGEX_REPLACE with a capture group:

REGEXP_REPLACE(col, r",\"([a-zA-Z0-9]*)\"", "\\1")

Here I assume col would have a value like:

,"AQk8tmAg94ZUZwqYKd6kHrswiVZR0wKNuTvSpr6COCLpki"

which you mentioned in your question.

user_18632290

below example is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT ",\"AQk8tmAg94ZUZwqYKd6kHrswiVZR0wKNuTvSpr6COCLpki\"" string1
)
SELECT REGEXP_EXTRACT(string1, r'([a-zA-Z0-9]+)') string2
FROM `project.dataset.table`

with result 

Row string2  
1   AQk8tmAg94ZUZwqYKd6kHrswiVZR0wKNuTvSpr6COCLpki

or just simply 

SELECT REGEXP_EXTRACT(",\"AQk8tmAg94ZUZwqYKd6kHrswiVZR0wKNuTvSpr6COCLpki\"", r'([a-zA-Z0-9]+)')

Btw, note: BigQuery provides regular expression support using the re2 library - where (?<=re) is not supported. Meantime, from your example it is not clear why you would need it as above examples do the work without it

Meantime, if you are looking for regexp that is the closest to yours (in question) - you can use r'(?:,\")([a-zA-Z0-9]*)' as in below example 

SELECT REGEXP_EXTRACT(",\"AQk8tmAg94ZUZwqYKd6kHrswiVZR0wKNuTvSpr6COCLpki\"", r'(?:,\")([a-zA-Z0-9]*)')
user_52219440
Related