Google BigQuery String Functions Explained

Formulas & functions · Updated November 22, 2026

Google BigQuery string functions are built-in SQL functions that let you manipulate, extract, format, and analyze text data directly in your queries. They include functions like CONCAT, SUBSTR, REPLACE, SPLIT, REGEXP_EXTRACT, and many others that work on STRING data types in BigQuery’s standard SQL.

Why String Functions Matter in BigQuery

BigQuery stores massive amounts of data, and a large portion of that data is text: user names, product descriptions, log messages, URLs, JSON fields, and free-form survey responses. Without string functions, you’d be stuck with raw text that you can’t filter, clean, or transform. String functions let you:

  • Clean messy data by trimming whitespace, removing characters, or standardizing case.
  • Extract meaningful parts from structured text like email addresses, URLs, or log lines.
  • Combine fields from different columns into a single value for reporting or export.
  • Search for patterns using regular expressions, which are essential for parsing unstructured text.

BigQuery’s string functions are part of its standard SQL dialect (as opposed to legacy SQL). They are designed to be fast and scalable, processing terabytes of text without performance degradation.

Core String Functions You’ll Use Daily

Here are the most common string functions in BigQuery, with a short example for each. All examples assume a table named users with a column email.

  • CONCAT(str1, str2, ...) – Joins strings together. Example: CONCAT(first_name, ' ', last_name) returns a full name.
  • SUBSTR(value, position[, length]) – Extracts a substring. Example: SUBSTR(email, 1, 5) returns the first five characters of the email.
  • REPLACE(value, from, to) – Replaces all occurrences of a substring. Example: REPLACE(email, '@', ' at ') returns the email with ‘@’ replaced.
  • TRIM(value) – Removes leading and trailing whitespace (also LTRIM and RTRIM for one side).
  • UPPER(value) / LOWER(value) – Converts to uppercase or lowercase.
  • LENGTH(value) – Returns the number of characters in the string.
  • STARTS_WITH(value, prefix) – Returns TRUE if the string starts with the prefix.
  • SPLIT(value, delimiter) – Splits a string into an array of substrings. Example: SPLIT(email, '@') returns an array like ['user', 'example.com'].

These functions are the building blocks for most text manipulation tasks. For example, to extract the domain from an email, you could use: SUBSTR(email, STRPOS(email, '@') + 1). The STRPOS function returns the position of the ‘@’ character.

Regular Expression Functions for Advanced Parsing

When you need to find patterns that are more complex than a fixed substring, BigQuery provides regular expression functions. These are especially useful for parsing log files, URLs, or any semi-structured text.

  • REGEXP_CONTAINS(value, regex) – Returns TRUE if the string contains a match for the regex.
  • REGEXP_EXTRACT(value, regex) – Returns the first substring that matches the regex (capturing group if used).
  • REGEXP_EXTRACT_ALL(value, regex) – Returns an array of all matches.
  • REGEXP_REPLACE(value, regex, replacement) – Replaces all matches with a replacement string.
  • REGEXP_INSTR(value, regex) – Returns the position of the first match.

For example, to extract the domain from an email using a regex: REGEXP_EXTRACT(email, r'@([^@]+)$'). The r prefix denotes a raw string in BigQuery, which is required for regex patterns. This pattern captures everything after the ‘@’ that is not another ‘@’.

How to Choose the Right Function

With so many functions, it’s easy to get overwhelmed. Here’s a simple decision guide:

  • Need to combine strings? Use CONCAT or the || operator (BigQuery supports both).
  • Need to extract a fixed portion? Use SUBSTR with a known position and length.
  • Need to find a variable pattern? Use REGEXP_EXTRACT.
  • Need to clean up whitespace? Use TRIM, LTRIM, or RTRIM.
  • Need to replace characters? Use REPLACE for simple substitutions, or REGEXP_REPLACE for pattern-based replacements.
  • Need to split into parts? Use SPLIT and then access array elements with OFFSET or ORDINAL.

Remember that BigQuery’s string functions are case-sensitive by default. If you need case-insensitive comparisons, use LOWER or UPPER on both sides.

Common Pitfalls and How to Avoid Them

Even experienced users make mistakes with string functions. Here are the most common pitfalls:

  • Off-by-one errors in SUBSTR: The position is 1-based, so SUBSTR('abc', 2, 1) returns ‘b’, not ‘a’.
  • Forgetting the r prefix for regex: Without it, backslashes in the pattern are interpreted as escape sequences. Always use r'...'.
  • Not handling NULLs: Most string functions return NULL if any argument is NULL. Use IFNULL or COALESCE to provide defaults.
  • Using SUBSTR on non-ASCII characters: BigQuery’s SUBSTR works on characters, not bytes, so it’s safe for UTF-8. But if you need byte-level operations, use SUBSTR with the BYTE mode (e.g., SUBSTR(value, 1, 2 BYTE)).
  • Assuming SPLIT returns a scalar: It returns an array. To get the first element, use SPLIT(email, '@')[OFFSET(0)].

Another common misconception is that REGEXP_EXTRACT returns all matches. It only returns the first. Use REGEXP_EXTRACT_ALL to get an array of all matches.

Performance and Cost Considerations

String functions in BigQuery are generally efficient, but they can affect query cost because BigQuery charges based on the amount of data processed. Here are a few tips:

  • Use REGEXP_CONTAINS in WHERE clauses sparingly, as it may prevent partition pruning if used on a partitioned column.
  • When possible, transform strings during data ingestion (e.g., in a pipeline) rather than in every query.
  • Avoid using SPLIT on large strings if you only need one part; use SUBSTR with STRPOS instead.
  • Remember that functions like LOWER and UPPER can prevent the use of indexes (though BigQuery doesn’t have traditional indexes, it does have clustering). If you filter on a case-insensitive column, consider storing the data in a consistent case.

BigQuery’s query cost is based on the bytes read, not the CPU time. So even complex string operations are fine if they don’t increase the amount of data scanned. Use SELECT to limit columns, and use WHERE to filter early.

Comparing BigQuery String Functions to Other SQL Dialects

If you’re coming from another database, you’ll find BigQuery’s functions similar but with some differences:

  • vs. MySQL: MySQL uses SUBSTRING instead of SUBSTR (though both work in BigQuery). MySQL’s REGEXP is a boolean operator, not a function; BigQuery uses REGEXP_CONTAINS.
  • vs. PostgreSQL: PostgreSQL uses SUBSTRING with a different syntax for regex (SUBSTRING(value FROM pattern)). BigQuery’s SPLIT is similar to PostgreSQL’s string_to_array.
  • vs. SQL Server: SQL Server uses CHARINDEX instead of STRPOS, and LEFT/RIGHT instead of SUBSTR with negative positions (which BigQuery doesn’t support).

One unique feature in BigQuery is the FORMAT function, which works like printf in C and is useful for building strings with numbers and dates. For example, FORMAT('%s has %d items', name, count).

Frequently Asked Questions

How do I concatenate strings with a separator in BigQuery?
Use CONCAT with explicit separators, or use ARRAY_TO_STRING if you have an array. For example, CONCAT(first, ' ', last) or ARRAY_TO_STRING(['a','b','c'], ',').

Can I use string functions on columns with NULL values?
Most string functions return NULL if any argument is NULL. To avoid this, use IFNULL(column, 'default') before applying the function. For example, CONCAT(IFNULL(first, ''), ' ', IFNULL(last, '')).

How do I extract the domain from an email address?
Use REGEXP_EXTRACT(email, r'@([^@]+)$') or SUBSTR(email, STRPOS(email, '@') + 1). The regex approach is more robust if the email has multiple ‘@’ (which is rare but possible in quoted strings).

What is the difference between SUBSTR and REGEXP_EXTRACT?
SUBSTR extracts a fixed position and length, while REGEXP_EXTRACT uses a pattern to find a variable-length match. Use SUBSTR when you know the exact position, and REGEXP_EXTRACT when the text varies.

How do I split a string into multiple rows in BigQuery?
Use SPLIT to get an array, then use UNNEST to expand the array into rows. For example: SELECT parts FROM UNNEST(SPLIT('a,b,c', ',')) AS parts.

Are BigQuery string functions case-sensitive?
Yes, by default. For case-insensitive comparisons, use LOWER(column) or UPPER(column) on both sides. There is no built-in ILIKE like in PostgreSQL.

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.