CODEWITHSUNDEEP
google-bigquery
LAHU BHAWAR
LAHU BHAWAR

Reputation: 113

how to find the count of substring in string using BigQuery?

I want to find how many times "fizz" appears in "fizzbuzzfizz" string in bigquery or sql. here output should be 2.

Upvotes: 2

Views: 5796

Answers (1)

Tamir Klein
Tamir Klein

Reputation: 3632

You can use REGEXP_EXTRACT_ALL and ARRAY_LENGTH, See this sql:

WITH data AS(
  SELECT 'fizzbuzzfizz' as string
  )

SELECT
  ARRAY_LENGTH(REGEXP_EXTRACT_ALL(string, "fiz")) AS size FROM data;

Which produces this:

enter image description here

Upvotes: 11

Related Questions