String Functions for Matching in BigQuery SQL

Formulas & functions · Updated November 22, 2026

BigQuery SQL provides several string functions for pattern matching, with REGEXP_CONTAINS being the primary tool for checking if a string matches a regular expression, and LIKE (or CONTAINS for simple substring checks) for basic wildcard matching. For extracting matched parts, use REGEXP_EXTRACT or REGEXP_EXTRACT_ALL, and for replacing, REGEXP_REPLACE.

Background: How BigQuery Handles Strings

BigQuery stores strings as UTF-8 encoded STRING type. Unlike some databases, BigQuery does not have a separate CHAR or VARCHAR; all are STRING. String functions in BigQuery are case-sensitive by default, meaning 'Apple' and 'apple' are different. If you need case-insensitive matching, you must explicitly convert with LOWER() or UPPER() before applying the pattern.

The underlying regular expression engine is RE2, a fast and safe regex library. RE2 supports most standard regex syntax but has some limitations—for example, it does not support backreferences or lookahead/lookbehind assertions. Knowing this prevents writing patterns that fail unexpectedly.

Core Functions: LIKE, REGEXP_CONTAINS, and CONTAINS

The simplest way to match a substring is CONTAINS(), which returns TRUE if the substring exists anywhere in the string.

SELECT CONTAINS('BigQuery is great', 'Query') AS result;  -- TRUE

For wildcard matching, use LIKE with two wildcards: % matches any sequence of characters (including zero), and _ matches exactly one character.

SELECT 'BigQuery' LIKE 'Big%' AS result;  -- TRUE
SELECT 'BigQuery' LIKE 'Big_Query' AS result;  -- TRUE (underscore matches 'Q')

For more complex patterns, REGEXP_CONTAINS is the workhorse. It takes a string and a regex pattern, returning TRUE if the pattern matches anywhere in the string.

SELECT REGEXP_CONTAINS('Order #12345', r'#\d{5}') AS result;  -- TRUE

Notice the r prefix before the pattern; it denotes a raw string, so backslashes are treated literally. Without it, you would need double backslashes (\\d) to escape the backslash for the SQL parser.

Extracting and Replacing Matched Text

When you need to pull out the matching part, use REGEXP_EXTRACT. It returns the first match of the pattern. If the pattern contains a capturing group (parentheses), it returns the first group.

SELECT REGEXP_EXTRACT('Order #12345', r'#(\d+)') AS order_number;  -- '12345'

To get all matches, use REGEXP_EXTRACT_ALL, which returns an array of all matches.

SELECT REGEXP_EXTRACT_ALL('emails: a@b.com, c@d.com', r'[\w.]+@[\w.]+') AS emails;  -- ['a@b.com', 'c@d.com']

For replacing, REGEXP_REPLACE replaces all occurrences of the pattern with a replacement string. You can reference capturing groups in the replacement using \\1, \\2, etc.

SELECT REGEXP_REPLACE('John Smith', r'(\w+) (\w+)', '\\2, \\1') AS reversed;  -- 'Smith, John'

If you only need simple substring replacement without regex, use REPLACE().

Choosing the Right Function: A Practical Guide

Deciding which function to use depends on the complexity of your pattern and what you want to do with the match.

  • Simple substring check (no wildcards): CONTAINS() is fastest and most readable.
  • Wildcard at start or end (e.g., prefix or suffix): LIKE is intuitive and sufficient.
  • Complex patterns (alternation, character classes, anchors): REGEXP_CONTAINS.
  • Extract a specific part: REGEXP_EXTRACT for first match, REGEXP_EXTRACT_ALL for all.
  • Replace text: REGEXP_REPLACE for regex-based, REPLACE() for literal.

For case-insensitive matching, always normalize with LOWER() or UPPER() on both sides. For example, to find ‘apple’ regardless of case:

SELECT LOWER('Apple pie') LIKE '%apple%' AS result;  -- TRUE

Common Pitfalls and How to Avoid Them

One frequent mistake is forgetting that LIKE and REGEXP_CONTAINS are case-sensitive. Another is misusing the raw string prefix. Without r, you must escape backslashes, which makes patterns hard to read. Always use r'...' for regex patterns.

Another pitfall: REGEXP_EXTRACT returns NULL if no match is found, which can cause issues if you expect a default value. Use IFNULL() to handle NULLs:

SELECT IFNULL(REGEXP_EXTRACT('No number here', r'\d+'), 'none') AS extracted;  -- 'none'

Also, beware of RE2 limitations. If you try to use a lookahead like (?=foo), BigQuery will throw an error. Rewrite your pattern without lookarounds, or use a different approach like splitting the string.

Performance: for large tables, regex operations can be expensive. If you only need prefix matching, consider using STARTS_WITH() or ENDS_WITH() functions, which are optimized and simpler.

Comparing with Other Databases

If you’re coming from PostgreSQL, note that BigQuery’s REGEXP_CONTAINS is similar to ~ operator, and REGEXP_EXTRACT is like substring() with a regex. In MySQL, you’d use REGEXP_LIKE() and REGEXP_SUBSTR(). BigQuery’s functions are more explicit and consistent in naming.

One difference: BigQuery’s LIKE does not support escape characters by default. If you need to match a literal % or _, you must use ESCAPE clause, but BigQuery does not support it. Instead, use REGEXP_CONTAINS with escaped characters: r'%' matches a literal percent sign.

Advanced Techniques: Using Arrays and UNNEST

When you have a string that contains multiple matches and you want to process each one, combine REGEXP_EXTRACT_ALL with UNNEST to turn the array into rows.

WITH data AS (
  SELECT 'Contact: a@b.com, c@d.com' AS text
)
SELECT email
FROM data, UNNEST(REGEXP_EXTRACT_ALL(text, r'[\w.]+@[\w.]+')) AS email;
-- Returns two rows: a@b.com, c@d.com

You can also use REGEXP_CONTAINS in a WHERE clause to filter rows efficiently. For example, to find all orders with a 5-digit number:

SELECT order_id
FROM orders
WHERE REGEXP_CONTAINS(order_description, r'\b\d{5}\b');

Remember to use word boundaries \b to avoid matching longer numbers.

Frequently Asked Questions

How do I do a case-insensitive search in BigQuery?
Use LOWER() on both the column and the pattern. For example: WHERE LOWER(name) LIKE '%john%'. Alternatively, use REGEXP_CONTAINS(LOWER(name), r'john'). There is no built-in case-insensitive flag in BigQuery regex.

What is the difference between LIKE and REGEXP_CONTAINS?
LIKE supports only two wildcards (% and _) and is simpler and faster for basic patterns. REGEXP_CONTAINS supports full regular expressions, allowing complex patterns like alternation (cat|dog), character classes ([0-9]), and anchors (^ and $).

Can I use regex to extract a substring between two delimiters?
Yes, use REGEXP_EXTRACT with a pattern like r'start(.*?)end'. The non-greedy .*? ensures you capture the shortest match. For example, REGEXP_EXTRACT('start123end', r'start(.*?)end') returns ‘123’.

Why does my regex fail with an error about lookahead?
BigQuery uses RE2, which does not support lookahead or lookbehind assertions. Rewrite your pattern to avoid them. For example, instead of r'foo(?=bar)', use r'foobar' and then check for ‘bar’ separately.

Is there a function to split a string by a regex?
Yes, use REGEXP_EXTRACT_ALL to get all matches, or use SPLIT() with a literal delimiter. For regex-based splitting, you can use REGEXP_REPLACE to replace the delimiter with a special character, then SPLIT() on that character, but it’s often easier to use REGEXP_EXTRACT_ALL to capture the parts you want.

Cost and Performance Considerations

BigQuery charges per query based on the amount of data processed. Using REGEXP_CONTAINS or LIKE in a WHERE clause requires scanning the entire column unless the table is partitioned or clustered. To reduce costs, filter on partitioned columns first, or use WHERE conditions that can leverage clustering.

For extremely large tables, consider pre-processing strings during data ingestion to extract fields you need, so queries don’t have to run regex on every row. Also, avoid using regex on columns that are not indexed—BigQuery does not support traditional indexes, but clustering can help with certain filters.

Finally, test your regex patterns on a small sample before running on the full dataset. Use the LIMIT clause to preview results and validate correctness.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.