How to get count of matches of a regexp in a string in bigquery?

1
votes

How would one go about getting the count of matches of a reqular expression in a string in bigquery? I don't think bigquery sql supports regexp_count.

So, for example, you might want to count the matches of "foo* " in a string like "foo1 foo foo40" and it would return a count of 2 (in this case, I am meaning that the "*" must be a non-empty string).

Thanks!

1
regexgoogle-bigquery

1 Answers

3
votes

Below is for BigQuery Standard SQL

You can use combination of array_length and regexp_extract_all as in example below 

#standardSQL
WITH `project.dataset.table` AS (
SELECT "foo1 foo foo40" str
)
SELECT array_length(regexp_extract_all(str, r'foo[^\s]')) matches
FROM `project.dataset.table`