How To Capture More than Group When Using BigQuery Regex

Oluwabukunmi Ige — Sat, 15 May 2021 16:21:07 +0000

I have been working on a particular task that required me to extract dates from a pool of text that was stored Big query. Trust me when I say that the data was quite messy and every date format you can imagine was in attendance.

Hence I got to work. Went through the big query docs and the function that was more suitable for my task was REGEXP_EXTRACT. How it was supposed to work was that I would specify the date format I wanted to extract in the regex function and it would extract it for me.
Something like this:

SELECT REGEXP_EXTRACT('210118164541-1/18/2021',r"(0[1-9]|1[0-2])(/)(0[1-9]|[12]\d|30|31)(/)(\d{4})")

In this case, the date was 1/18/2021 and the job of the regex function was to extract the date.

The issue I encountered was that the regex_extract function doesn't work with more than one capturing group. What I mean by capturing group is mainly due to the fact that I could not predict the date format in the data and each part of the date needed a different capturing group. Something like
DD/MM/YY or MM/DD/YY

Unfortunately, the regex extract function only accepted one capturing group.

Citing that Regular expressions passed into extraction functions must not have more than 1 capturing group

Did so some google and Stackoverflow searches before was able to figure out a solution.

The solution was that since REGEXP_EXTRACT only takes one capturing group you had to make the other groups non-capturing using ?:. This allows the function to capture more than one group.

The code below shows this in practice.

SELECT REGEXP_EXTRACT('210118164541-1/18/2021',r"(?:0[1-9]|[12]\d|30|31)(?:.)(?:0[1-9]|1[0-2])(?:/)(?:\d{4}|\d{2}")

The code above would perfectly extract the date 1/18/2021 as needed.

In conclusion, if you ever encounter a task that requires you to extract more than one capturing group using regex on BigQuery remember that you have to make the other groups non-capturing using the ?: notation and you are good to go.

DEV Community: Oluwabukunmi Ige

How To Capture More than Group When Using BigQuery Regex