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.

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