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.

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.