BigQuery LIKE ALL and LIKE ANY: Multi-Pattern Matching

Let's look at the quantified LIKE operator in BigQuery. Why quantified? , you'll ask.

So while the normal LIKE operator can be used to check just for one pattern, the quantified one (still in preview btw) can check the presence of one or all patterns from a sequence we provide. Here's how the syntax looks.

WHERE text LIKE ALL ('%dog%', '%fox%') - this will check if ALL the patterns are present in the text

WITH input_data AS 

(
  SELECT 'The quick brown fox jumps over the lazy dog' AS text
  UNION ALL
  SELECT 'My favorite dog is a Labrador' AS text
)

SELECT text FROM input_data

WHERE text LIKE ALL ('%dog%', '%fox%')

-- returns only the first row, since only that one has both dog and fox

WHERE text LIKE SOME ('%dog%', '%fox%') and
WHERE text LIKE ANY ('%dog%', '%fox%') - these two are synonyms and will check if AT LEAST ONE of the listed patterns is present in the text

WITH input_data AS 

(
  SELECT 'The quick brown fox jumps over the lazy dog' AS text
  UNION ALL
  SELECT 'My favorite dog is a Labrador' AS text
)

SELECT text FROM input_data

WHERE text LIKE ALL ('%dog%', '%fox%')
-- returns both rows, since both match at least one pattern

Pair them with the NOT keyword to achieve the opposite effect, so NOT LIKE ANY/SOME means not even a partial match and NOT LIKE ALL means no full match.

Thanks for reading!

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

LIKE ALL and LIKE ANY in BigQuery

Comments

Practical SQL

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

More from this blog