BigQuery LIKE ANY: How to Match a Column Against a List of Patterns

BigQuery LIKE ANY matches a string column against several patterns at once and returns true if the value matches any one of them. Instead of chaining a long list of OR conditions, you write a single expression: the column, the LIKE ANY operator, and a parenthesized list of patterns. BigQuery also supports LIKE ALL, which requires every pattern to match, and NOT LIKE ANY, which excludes rows matching any pattern in the list. These quantified LIKE operators are part of GoogleSQL, BigQuery's standard dialect.

The syntax

The pattern is straightforward. You test one column against a list of LIKE patterns, each using the usual wildcards: the percent sign for any sequence of characters and the underscore for a single character.

Read the first row out loud and it explains itself: keep the row if the product name contains "pro" or "plus" or "max." Before these operators existed, you wrote that as three separate LIKE conditions joined by OR, which is fine for three patterns and miserable for thirty. LIKE ANY collapses the whole list into one readable line.

When to use LIKE ANY versus OR

Functionally, LIKE ANY with a list of patterns does the same job as several LIKE conditions joined by OR. The difference is readability and maintenance. A LIKE ANY list is easy to scan and easy to extend: add a pattern to the list and you are done. A long OR chain is harder to read, easier to break with a misplaced parenthesis, and more likely to hide a duplicate.

Reach for LIKE ANY whenever you are checking one column against a set of known substrings: matching product names against a family of SKUs, filtering transactions whose description contains any of a list of keywords, or excluding a set of internal email domains. It keeps the intent, "does this contain any of these," visible in the query instead of buried in boolean noise.

Does BigQuery support LIKE ANY?

Yes. The quantified LIKE operator is generally available in GoogleSQL, BigQuery's standard dialect, and covers LIKE ANY, LIKE SOME, LIKE ALL and their negations. SOME is an exact synonym for ANY, so LIKE SOME ('%a%', '%b%') and LIKE ANY ('%a%', '%b%') behave identically. Pick one and stay consistent across your codebase.

If a LIKE ANY query throws a syntax error, the usual cause is not BigQuery. Check that you are not running the query through a tool that rewrites SQL for a different engine, and check that you are in GoogleSQL rather than Legacy SQL, which does not support it. Our note on Standard SQL versus Legacy SQL covers how to tell which dialect a query is running under.

BigQuery NOT LIKE ANY vs NOT LIKE ALL

This pair catches almost everybody, because the intuitive reading of NOT LIKE ALL is wrong. NOT LIKE ANY requires the value to match none of the patterns. NOT LIKE ALL is satisfied as long as the value fails to match at least one pattern, so a value matching some but not all of the patterns still passes the filter.

If you are cleaning test accounts out of a report, use NOT LIKE ANY. Reaching for NOT LIKE ALL is a common way to write a filter that looks correct, runs without error, and quietly leaves rows in your numbers.

How LIKE ANY handles NULLs and empty lists

The documented semantics matter here because they are not what you would guess, and a wrong guess shows up as missing rows rather than an error. For LIKE ANY and LIKE SOME, BigQuery returns FALSE when the pattern list is empty, returns NULL when the search value is NULL, returns TRUE when the search value matches at least one pattern, and returns NULL when one of the patterns is NULL and none of the others match.

The practical consequence is the familiar SQL three-valued logic trap: a WHERE clause keeps only rows that evaluate to TRUE, so rows returning NULL are dropped as surely as rows returning FALSE. If a nullable column matters, handle it explicitly with IFNULL(col, '') or an OR col IS NULL branch rather than assuming the filter will pass them through. Our guide to COALESCE and IFNULL in BigQuery covers the tidiest way to do that.

What is the difference between LIKE ANY and IN?

IN tests for exact equality against a list of values. LIKE ANY tests for pattern matches against a list of patterns, so wildcards work. Use IN when you have the complete values, for example WHERE country IN ('US', 'CA'). Use LIKE ANY when you only have fragments, for example WHERE product LIKE ANY ('%pro%', '%plus%').

They are not interchangeable, and the failure is silent in one direction. Writing WHERE product IN ('%pro%') is valid SQL that returns nothing, because it looks for a value literally equal to the six characters %pro%. If a filter you expected to match rows returns zero, check whether you used IN with wildcards.

BigQuery LIKE with multiple values from a table

The pattern list in a quantified LIKE is written inline in the query. That is fine for a fixed set of patterns, and awkward when the patterns live in a table or arrive as a parameter that changes. For those cases, join instead of trying to inject a list.

-- keywords is a table with one pattern per row, e.g. '%pro%', '%plus%'
SELECT DISTINCT o.order_id, o.product
FROM sales.orders AS o
JOIN config.keywords AS k
  ON o.product LIKE k.pattern

A join on LIKE gives you one row per matching pattern, so use DISTINCT or an aggregate if a product could match several patterns and you only want it once. When you need a boolean flag rather than a filter, an EXISTS subquery reads better and avoids the duplicate-row problem entirely.

SELECT
  o.order_id,
  EXISTS (
    SELECT 1 FROM config.keywords AS k
    WHERE o.product LIKE k.pattern
  ) AS matches_keyword
FROM sales.orders AS o

Keeping the patterns in a table has a real advantage beyond syntax: a non-engineer can maintain the keyword list without anyone editing and redeploying SQL.

When to use REGEXP_CONTAINS instead

LIKE ANY is the right tool for simple substring and wildcard matching. Once your matching rules get more complex, whole-word boundaries, alternation with anchors, case-insensitive matching, or numeric patterns, you want a regular expression instead. BigQuery's REGEXP_CONTAINS lets you express all of that in a single pattern.

A rough guide: if you can describe the match as "contains one of these pieces of text," LIKE ANY is cleaner and usually faster. If you need "matches this shape," a phone number, a code with a checksum, a word only when it stands alone, use REGEXP_CONTAINS. Do not force a regular expression to do a simple contains check, and do not try to bend LIKE into doing real pattern logic. Each is clearest in its own lane.

Common mistakes

Two things trip people up. First, the patterns still need their wildcards. LIKE ANY ('pro', 'plus') matches only values that equal exactly "pro" or "plus," because without a percent sign LIKE is an exact match. If you mean "contains," wrap each pattern in percent signs. Second, remember that LIKE is case-sensitive in BigQuery, so "Pro" will not match "pro." Lowercase both sides with LOWER, or switch to a case-insensitive REGEXP_CONTAINS, when case should not matter.

Or skip the syntax entirely

If you landed here because you just needed to filter a BigQuery column against a list and were not sure of the exact operator, there is a faster path than memorizing it. Agentsql connects read-only to BigQuery, takes a plain-English request like "show orders whose product name contains pro, plus or max," writes the correct GoogleSQL, including the LIKE ANY or REGEXP_CONTAINS, runs it, and shows you the query so you can confirm it did what you meant. You get the answer and the exact SQL, without keeping the syntax in your head. See how Agentsql works with BigQuery, learn to query BigQuery without writing SQL, or explore the SQL query generator and ask your data a question.