BigQuery REGEXP_CONTAINS: How to Match Text with Regular Expressions

BigQuery REGEXP_CONTAINS returns TRUE when a string contains a match for a regular expression, so REGEXP_CONTAINS(column, r'pattern') is how you filter rows by a text pattern in BigQuery Standard SQL. It uses the RE2 regex library, it is case-sensitive by default, and it is the right tool when a plain LIKE with % wildcards cannot express what you need. Below is the syntax, the patterns people reach for most (matching several values at once, negating a match, ignoring case), how it compares to LIKE, and the mistakes that trip people up.

Syntax and a first example

The function takes two arguments: the string (a column or expression) and the regex pattern. It returns a boolean, so it belongs in a WHERE clause or anywhere a condition fits.

SELECT order_id, status
FROM sales.orders
WHERE REGEXP_CONTAINS(status, r'shipped')

That returns every row whose status contains the substring "shipped" anywhere in the value. The r'...' is a raw string, and you should almost always use it for patterns so that backslashes are passed through to the regex engine instead of being treated as SQL escapes.

Matching multiple values at once

The most common reason people switch from LIKE to REGEXP_CONTAINS is matching several values in one condition. Regex alternation with the pipe character does this cleanly, and it replaces a stack of OR ... LIKE clauses.

-- one condition instead of three ORs
SELECT order_id, status
FROM sales.orders
WHERE REGEXP_CONTAINS(status, r'shipped|delivered|returned')

If you want whole-word or exact matches rather than substrings, anchor the pattern. ^ matches the start of the string and $ matches the end, so r'^(shipped|delivered)$' matches only those exact statuses and nothing that merely contains them.

Case-insensitive matching

REGEXP_CONTAINS is case-sensitive, so "Gmail" and "gmail" are different. Rather than wrapping the column in LOWER(), add the inline flag (?i) at the start of the pattern to make the match case-insensitive.

SELECT email
FROM users
WHERE REGEXP_CONTAINS(email, r'(?i)gmail\.com$')

Notice the \. in that pattern. A bare dot in regex matches any character, so to match a literal period in a domain you escape it. Forgetting to escape the dot is one of the most common regex bugs, because r'gmail.com' would also match "gmailxcom".

Negating a match with NOT

To find rows that do not match, put NOT in front of the whole call. This is clearer than trying to write a "does not contain" pattern inside the regex.

SELECT email
FROM users
WHERE NOT REGEXP_CONTAINS(email, r'(?i)@(test|example)\.com$')

One thing to know: if the input string is NULL, REGEXP_CONTAINS returns NULL, not FALSE, and a NULL condition excludes the row. If you need NULLs treated as non-matches under a NOT, wrap the column with IFNULL(email, '') so the comparison is defined.

REGEXP_CONTAINS vs LIKE: which to use

Use LIKE for simple substring or prefix matches and REGEXP_CONTAINS when you need real pattern logic. LIKE is simpler to read and can be a touch faster on plain patterns, while regex gives you alternation, anchors, character classes, and quantifiers that LIKE cannot express.

If you have been filtering with several LIKE ANY style workarounds, our note on how to match multiple values in BigQuery shows where regex is the cleaner path and where an IN list or UNNEST is better.

Patterns worth keeping around

• US ZIP code: r'^[0-9]{5}(-[0-9]{4})?$' matches five digits with an optional four-digit extension.
• Rough email shape: r'^[^@\s]+@[^@\s]+\.[^@\s]+$' for a sanity check, not full validation.
• Contains any digit: r'[0-9]', useful for flagging free-text fields that should be numeric.
• Whitespace-only value: r'^\s*$' to find blank-looking strings that are not truly NULL.

RE2, the engine BigQuery uses, is fast and safe but does not support backreferences or lookahead, so patterns that rely on those will error. For most filtering work you will never hit that limit. Regex also shines when you are cleaning messy text you pulled in from somewhere else, the kind of raw input a web scraping pipeline that returns clean, structured data produces before it lands in your warehouse.

Ask it in plain English instead

You do not have to remember RE2 syntax to filter your data. Agentsql connects read-only to BigQuery, turns a plain-English question like "orders that shipped or were delivered last week" into the correct Standard SQL, runs it, and shows you the query it wrote, REGEXP_CONTAINS and all, so you can verify it. See the BigQuery integration for how it connects and the SQL query generator for how the translation works, then see how it works and ask BigQuery a question in plain English.