Extract all pattern occurrences in BigQuery

If you ever need to extract information based on a pattern in a BigQuery string, check out the REGEXP_EXTRACT_ALL function.

This will return an array of all the occurrences matching the specified regular expression.

With regards to the pattern itself, I typically use a representative example with a regex debugger like regex101.

Worth noting that it has a limitation - it would only work with a single regex capture group, so you can't match multiple patterns at the same time.

No alt text provided for this image

Found it useful? Check out to my Analytics newsletter at notjustsql.com.