12 Aug 20 · npack · #Bigquery ·   Bookmark   ×

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

npack

posted on 12 Aug 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds