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)andLOWER(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 offromwithto. Example:REPLACE('a-b-c', '-', '/')returns ‘a/b/c’.STARTS_WITH(value, prefix)andENDS_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 ofsubstring, 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 useUNNESTto expand the array into rows.REPEAT(value, n)– Repeats a stringntimes. Example:REPEAT('ab', 3)returns ‘ababab’.LPAD(value, length[, pad])andRPAD(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
WHEREclauses to reduce the number of rows processed. - Avoid applying functions on columns used in
JOINkeys; preprocess the data or use a derived table. - Consider using
REGEXP_CONTAINSwith 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, useSPLIT(...)[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
- Excel Inventory Template: How to Track Stock
- Excel Template Job Schedule: How to Build One
- Vacation Budget Excel Template: How to Plan & Track
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Cell Color by Hex: Apply, Match, and DisplayYes. In Excel for Microsoft 365, you can apply a cell fill that corresponds to a hex color,…
- Excel Day Count Formula: Calculate Days Between DatesTo count calendar days between two dates in Excel, subtract the earlier date from the later one: with…
- Excel Conditional Formatting Formula Examples That WorkExcel conditional formatting formulas let you highlight cells or entire rows when a logical test is true. In…
- How to Concatenate Names in Excel With a Space EasilyTo concatenate first and last names in Excel with a space, put the first name in A2 and…
- Excel COUNTIF Year in Date: Formula & ExamplesTo count cells containing a date in a specific year, use =SUMPRODUCT(--(YEAR(range)=year)) or, in Excel 2021 and Microsoft…
- Excel Formula to Extract the First Five Digits from D2Assuming D2 contains the text or number you want to search, use =IFERROR(IF(D2="","",LEFT(TEXTJOIN("",TRUE,IFERROR(MID(D2,SEQUENCE(LEN(D2)),1)*1,"")),5)),"") to collect the first five…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.