How to find the number of occurences of a string in a string column in Bigquery

How to find how many times "Jazz" appears in "JazzbuzzJazz" string in Bigquery using SQL. The output should be 2 in this example.

In order to find the count of substring in string using BigQuery, use the below solution.

Solution:

You can use REGEXP_EXTRACT_ALL and ARRAY_LENGTH

WITH data AS(
  SELECT 'JazzbuzzJazz' as string
  )
SELECT
  ARRAY_LENGTH(REGEXP_EXTRACT_ALL(string, "Jazz")) AS size FROM data;

Result:

2

nVector

posted on 12 Aug 20