BigQuery SQL String Functions: The Essential Guide

Formulas & functions · Updated November 22, 2026

BigQuery SQL string functions are built-in operations that let you manipulate, extract, transform, and analyze text data directly in your queries—covering everything from concatenation and substring extraction to case conversion, trimming, and regex matching. This guide explains the most useful functions, how to use them with real examples, and how to avoid common pitfalls.

Why String Functions Matter in BigQuery

In BigQuery, string data is stored as STRING type (UTF-8 encoded) and is ubiquitous in real-world datasets: user names, addresses, product codes, log messages, URLs, JSON blobs, and free-text fields. Without string functions, you would have to export data to another tool just to clean or reshape text. BigQuery’s string functions let you do that work in SQL, keeping your pipeline simple and fast. They are also essential for data cleaning (removing extra spaces, standardizing formats), feature engineering for machine learning, and generating reports that require human-readable output.

Core String Functions: The Building Blocks

These are the functions you’ll use most often. All examples assume a table named users with a column name containing ‘ Alice Smith ‘.

  • CONCAT(a, b, ...) – Joins two or more strings together. Example: CONCAT(first_name, ' ', last_name) returns ‘John Doe’.
  • SUBSTR(value, position[, length]) – Extracts a substring. Position starts at 1. Example: SUBSTR('BigQuery', 4) returns ‘Query’.
  • TRIM(value[, chars]) – Removes leading and trailing whitespace (or specified characters). Example: TRIM(' Alice Smith ') returns ‘Alice Smith’.
  • UPPER(value) and LOWER(value) – Convert to uppercase or lowercase. Example: UPPER('hello') returns ‘HELLO’.
  • LENGTH(value) – Returns the number of characters in the string. Example: LENGTH('hello') returns 5.
  • REPLACE(value, from, to) – Replaces all occurrences of from with to. Example: REPLACE('a-b-c', '-', '/') returns ‘a/b/c’.
  • STARTS_WITH(value, prefix) and ENDS_WITH(value, suffix) – Return TRUE if the string starts or ends with the given substring.
  • STRPOS(value, substring) – Returns the 1-based position of the first occurrence of substring, or 0 if not found.

Advanced Functions: Splitting, Repeating, and Padding

Beyond the basics, BigQuery offers functions that handle more complex text operations:

  • SPLIT(value, delimiter) – Splits a string into an array of substrings. Example: SPLIT('apple,banana,orange', ',') returns [‘apple’, ‘banana’, ‘orange’]. You can then use UNNEST to expand the array into rows.
  • REPEAT(value, n) – Repeats a string n times. Example: REPEAT('ab', 3) returns ‘ababab’.
  • LPAD(value, length[, pad]) and RPAD(value, length[, pad]) – Pads the string to a target length with a pad string (default space). Example: LPAD('7', 3, '0') returns ‘007’.
  • REVERSE(value) – Reverses the string. Example: REVERSE('abc') returns ‘cba’.
  • FORMAT(format_string, ...) – Formats a string using printf-style placeholders. Example: FORMAT('%s has %d items', 'Cart', 3) returns ‘Cart has 3 items’.
  • REGEXP_CONTAINS(value, regex) – Returns TRUE if the string matches the regular expression. Useful for pattern validation.
  • REGEXP_EXTRACT(value, regex) – Extracts the first matching substring. Example: REGEXP_EXTRACT('Order #123', r'#(\d+)') returns ‘123’.
  • REGEXP_REPLACE(value, regex, replacement) – Replaces all matches of the regex with a replacement string.

Practical Examples: Cleaning and Transforming Data

Let’s walk through a common scenario: you have a column email with values like ‘ USER@Example.COM ‘. You want to normalize it to lowercase and remove spaces. Use:

SELECT LOWER(TRIM(email)) AS normalized_email FROM users;

Another example: extract the domain from an email address. Use SPLIT and array indexing:

SELECT SPLIT(email, '@')[OFFSET(1)] AS domain FROM users;

Or with REGEXP_EXTRACT:

SELECT REGEXP_EXTRACT(email, r'@(.+)$') AS domain FROM users;

If you need to split a comma-separated list into rows, use UNNEST:

SELECT id, item FROM orders, UNNEST(SPLIT(items, ',')) AS item;

Performance Considerations and Best Practices

String functions can be expensive on large datasets, especially regex operations. Here are tips to keep queries fast:

  • Use simple string functions (SUBSTR, TRIM) over regex when possible; regex is more CPU-intensive.
  • Filter early with WHERE clauses to reduce the number of rows processed.
  • Avoid applying functions on columns used in JOIN keys; preprocess the data or use a derived table.
  • Consider using REGEXP_CONTAINS with anchored patterns (e.g., ^prefix) to speed up matching.
  • When using SPLIT, be aware that it creates an array; if you only need the first element, use SPLIT(...)[SAFE_OFFSET(0)] to avoid errors on empty strings.

Common Mistakes and Misconceptions

One frequent mistake is assuming SUBSTR is zero-indexed. In BigQuery, positions start at 1. Another is forgetting that LENGTH counts characters, not bytes; for UTF-8, a character may be multiple bytes, but LENGTH returns the number of characters. Also, TRIM only removes spaces by default; if you need to remove other characters, specify them as the second argument. Finally, REPLACE replaces all occurrences, not just the first—use REGEXP_REPLACE with a pattern if you need more control.

A common misconception is that CONCAT ignores NULLs. In BigQuery, CONCAT returns NULL if any argument is NULL. To treat NULL as an empty string, use IFNULL or COALESCE:

SELECT CONCAT(IFNULL(first_name, ''), ' ', IFNULL(last_name, '')) FROM users;

Comparing with Other SQL Dialects

If you’re coming from PostgreSQL or MySQL, you’ll notice some differences. BigQuery uses SUBSTR instead of SUBSTRING (though SUBSTRING is also accepted). It lacks a built-in CONCAT_WS function; you can emulate it with ARRAY_TO_STRING and IFNULL. BigQuery’s SPLIT returns an array, whereas in PostgreSQL you’d use string_to_array and in MySQL SUBSTRING_INDEX. Regex functions are similar to PostgreSQL’s but use REGEXP_CONTAINS instead of ~. For string aggregation, BigQuery uses STRING_AGG (similar to MySQL’s GROUP_CONCAT).

Frequently Asked Questions

Q: How do I concatenate strings with a separator in BigQuery?
A: Use CONCAT with the separator as a literal, e.g., CONCAT(a, '-', b). For a list of values, consider ARRAY_TO_STRING after using ARRAY_AGG.

Q: What is the difference between SUBSTR and REGEXP_EXTRACT?
A: SUBSTR extracts a substring based on position and length, while REGEXP_EXTRACT extracts based on a regular expression pattern. Use SUBSTR for fixed positions and REGEXP_EXTRACT for variable patterns.

Q: How can I remove all non-alphanumeric characters from a string?
A: Use REGEXP_REPLACE: REGEXP_REPLACE(value, r'[^a-zA-Z0-9]', '').

Q: Does BigQuery support LEFT and RIGHT functions?
A: No, but you can use SUBSTR(value, 1, n) for left and SUBSTR(value, -n) for right (negative position counts from the end).

Q: How do I check if a string contains a substring?
A: Use STRPOS(value, substring) > 0 or REGEXP_CONTAINS(value, substring).

Q: Are string functions case-sensitive?
A: Yes, by default. Use LOWER or UPPER to normalize before comparison.

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.