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.

WITH input_data AS (
  SELECT  """Occurences on 2021-01-01, 2021-01-02, 2021-01-03, 2021-01-04""" AS raw_data
)

SELECT
  REGEXP_EXTRACT_ALL(raw_data, r'\d{4}-\d{2}-\d{2}') AS extracted_dates
FROM input_data