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 (alsoLTRIMandRTRIMfor 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
CONCATor the||operator (BigQuery supports both). - Need to extract a fixed portion? Use
SUBSTRwith a known position and length. - Need to find a variable pattern? Use
REGEXP_EXTRACT. - Need to clean up whitespace? Use
TRIM,LTRIM, orRTRIM. - Need to replace characters? Use
REPLACEfor simple substitutions, orREGEXP_REPLACEfor pattern-based replacements. - Need to split into parts? Use
SPLITand then access array elements withOFFSETorORDINAL.
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, soSUBSTR('abc', 2, 1)returns ‘b’, not ‘a’. - Forgetting the
rprefix for regex: Without it, backslashes in the pattern are interpreted as escape sequences. Always user'...'. - Not handling NULLs: Most string functions return NULL if any argument is NULL. Use
IFNULLorCOALESCEto provide defaults. - Using
SUBSTRon non-ASCII characters: BigQuery’sSUBSTRworks on characters, not bytes, so it’s safe for UTF-8. But if you need byte-level operations, useSUBSTRwith theBYTEmode (e.g.,SUBSTR(value, 1, 2 BYTE)). - Assuming
SPLITreturns a scalar: It returns an array. To get the first element, useSPLIT(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_CONTAINSinWHEREclauses 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
SPLITon large strings if you only need one part; useSUBSTRwithSTRPOSinstead. - Remember that functions like
LOWERandUPPERcan 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
SUBSTRINGinstead ofSUBSTR(though both work in BigQuery). MySQL’sREGEXPis a boolean operator, not a function; BigQuery usesREGEXP_CONTAINS. - vs. PostgreSQL: PostgreSQL uses
SUBSTRINGwith a different syntax for regex (SUBSTRING(value FROM pattern)). BigQuery’sSPLITis similar to PostgreSQL’sstring_to_array. - vs. SQL Server: SQL Server uses
CHARINDEXinstead ofSTRPOS, andLEFT/RIGHTinstead ofSUBSTRwith 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
- Traffic Light Changer Device: How It Works & Legal Risks
- SPLIT Function in Google Sheets: How to Use It
- Shortcut Keys Save As: The Full Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- PV vs NPV in Excel: Which Function Should You Use?Use Excel’s PV function to find the present value of regular payments or a future balance; use NPV…
- How to Calculate Numbers in Excel With Simple FormulasTo calculate numbers in Excel, start a cell entry with an equals sign and use cell references or…
- Excel COUNTIF Contains Text from Another Cell?Yes, use COUNTIF(range, "*"&cell&"*") to count cells that contain the text from another cell. For example, =COUNTIF(A2:A10, "*"&B1&"*")…
- PostgreSQL Window Functions: Queries, Frames, and ExamplesPostgreSQL window functions calculate a result across related rows while keeping each row in the query result. Use…
- Excel Sort Dates by Quarter: Methods and TipsYes. In Excel for Microsoft 365 on Windows, add a helper column that calculates each date’s quarter, then…
- Rust String Functions: Methods, Examples, and TipsRust handles text mainly with the growable, owned String type and the borrowed string slice &str. Their methods…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.