String Functions BigQuery: The Essential Guide

Formulas & functions · Updated November 22, 2026

The essential string functions in BigQuery are CONCAT, SUBSTR, REPLACE, UPPER, LOWER, TRIM, SPLIT, REGEXP_EXTRACT, and REGEXP_REPLACE, which handle concatenation, extraction, substitution, case conversion, whitespace removal, and pattern-based parsing. These functions operate on STRING data types and are part of the standard SQL dialect that BigQuery uses by default.

Why String Functions Matter in BigQuery

BigQuery is a serverless data warehouse, meaning you write SQL and the service handles the infrastructure. String manipulation is one of the most common tasks in data cleaning and transformation because raw data rarely arrives in a clean format. Log files, user-generated input, and imported CSV files often contain inconsistent casing, extra spaces, embedded delimiters, or mixed formats. String functions let you normalize, parse, and reshape text directly in your query, avoiding the need to export data to another tool. They run on BigQuery’s massively parallel architecture, so even operations on billions of rows execute quickly without you managing any servers.

Core String Functions and How They Work

BigQuery’s standard string functions cover the basics you would expect from any SQL dialect, but with some Google-specific nuances. Here are the most frequently used ones, with examples:

  • CONCAT(value1, value2, ...) joins strings. Unlike some databases, BigQuery also supports the || operator. Example: SELECT CONCAT('Big', 'Query') returns BigQuery.
  • SUBSTR(value, position[, length]) extracts a substring. Positions start at 1. SELECT SUBSTR('BigQuery', 4, 5) returns Query. If length is omitted, it returns everything from position to the end.
  • REPLACE(value, from, to) substitutes all occurrences of a substring. SELECT REPLACE('a-b-c', '-', '/') returns a/b/c.
  • UPPER(value) and LOWER(value) convert case. UPPER('bigquery') returns BIGQUERY.
  • TRIM(value[, chars]) removes leading and trailing whitespace (or specified characters). TRIM(' hello ') returns hello. There are also LTRIM and RTRIM.
  • SPLIT(value, delimiter) returns an array of substrings. SPLIT('a,b,c', ',') returns ['a', 'b', 'c']. You can then use UNNEST to expand the array into rows.
  • LENGTH(value) returns the number of characters (not bytes). For bytes, use BYTE_LENGTH.
  • STARTS_WITH(value, prefix) and ENDS_WITH(value, suffix) return boolean checks.

Regular Expression Functions for Complex Patterns

When you need pattern matching beyond simple substring searches, BigQuery provides REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_EXTRACT_ALL, REGEXP_REPLACE, and REGEXP_INSTR. These use RE2 syntax, which is similar to Perl but with some differences—for example, lookahead assertions are not supported. A common use case is extracting a domain from an email address:

SELECT REGEXP_EXTRACT('user@example.com', r'@(.*)') AS domain;  -- returns 'example.com'

The r prefix denotes a raw string, so backslashes are treated literally. REGEXP_REPLACE works like REPLACE but with a pattern: SELECT REGEXP_REPLACE('phone 123-456', r'\d', '#') replaces all digits with #. Remember that in standard SQL, you need to escape backslashes in regular strings, but raw strings avoid that hassle.

Practical Examples and Common Workflows

Here are three realistic scenarios where you would combine these functions:

  1. Cleaning a name column: SELECT UPPER(TRIM(CONCAT(first_name, ' ', last_name))) AS full_name FROM users; This trims each part, concatenates with a space, and uppercases the result.
  2. Parsing a comma-separated list: If you have a column tags containing 'red,green,blue', you can turn it into rows: SELECT tag FROM dataset.items, UNNEST(SPLIT(tags, ',')) AS tag;
  3. Extracting a date from a log line: SELECT REGEXP_EXTRACT(log_line, r'\d{4}-\d{2}-\d{2}') AS event_date FROM logs;

Choosing Between SUBSTR and REGEXP_EXTRACT

The rule of thumb is: use SUBSTR when you know the exact position and length, and use REGEXP_EXTRACT when the pattern is variable. For example, extracting the year from a date string '2024-03-15' is easy with SUBSTR(date, 1, 4). But extracting the first word from a sentence of varying length is better with REGEXP_EXTRACT(sentence, r'^\w+'). Overusing regular expressions can hurt readability and performance, so start with the simpler function and only escalate when needed.

Performance and Cost Considerations

String functions themselves are not inherently expensive, but how you use them can affect query cost. BigQuery charges based on the amount of data processed, not the number of functions. Using REGEXP_REPLACE on a large text column processes the entire column, so it costs the same as a SELECT of that column. To minimize cost, filter rows with WHERE before applying expensive transformations, and avoid selecting large string columns unless necessary. Also, be aware that SPLIT can create many rows; if you only need the first element, use SPLIT(value, ',')[OFFSET(0)] instead of unnesting everything.

Frequently Asked Questions

Does BigQuery support CHARINDEX or INSTR?

No, BigQuery does not have CHARINDEX (SQL Server) or INSTR (Oracle). Instead, use STRPOS(value, substring), which returns the 1-based position of the first occurrence, or 0 if not found. Example: STRPOS('BigQuery', 'Query') returns 4.

How do I split a string into multiple rows?

Use SPLIT combined with UNNEST. For example: SELECT * FROM UNNEST(SPLIT('a,b,c', ',')) AS element; This returns three rows. If you have a column, you would join with UNNEST as shown earlier.

What is the difference between SUBSTR and SUBSTRING?

In BigQuery, they are synonyms. Both accept the same arguments and return the same results. You can use either name.

How do I remove all non-alphanumeric characters?

Use REGEXP_REPLACE with a character class. Example: SELECT REGEXP_REPLACE('Hello, World! 123', r'[^a-zA-Z0-9]', '') returns HelloWorld123. Note that this is case-sensitive; to include lowercase, add a-z.

Can I use string functions on BYTES data?

Some functions work on BYTES but with different semantics. For instance, SUBSTR works on bytes but counts bytes, not characters. CONCAT works on bytes too. However, UPPER, LOWER, and TRIM require STRING. Cast bytes to string first if needed: CAST(bytes_field AS STRING).

Are string functions case-sensitive?

Yes, by default. REPLACE('abc', 'A', 'x') returns abc because it does not match uppercase. For case-insensitive matching, use LOWER on both sides or use REGEXP_REPLACE with the (?i) flag, though RE2 supports inline flags like (?i) at the start of the pattern.

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.