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.