To split a string in BigQuery, use the SPLIT() function, which returns an array of substrings, or REGEXP_EXTRACT_ALL() for pattern-based splits. For example, SPLIT('a,b,c', ',') returns ['a', 'b', 'c'].
What SPLIT() Actually Returns
SPLIT() takes two arguments: the string to split and a delimiter. The delimiter can be a single character or a multi-character string. The result is an ARRAY<STRING>, not a table or a set of rows. This is a common point of confusion for newcomers.
SPLIT('apple;banana;cherry', ';')→['apple', 'banana', 'cherry']SPLIT('one-two-three', '-')→['one', 'two', 'three']SPLIT('hello', '')→['h', 'e', 'l', 'l', 'o'](empty delimiter splits into characters)
If the delimiter is not found, SPLIT() returns an array with the entire original string as the only element. If the string is empty, it returns an empty array [].
Turning an Array into Rows: UNNEST
Often you want each split part as a separate row, not an array. Use UNNEST() in the FROM clause. For example, with a table orders that has an items column like 'A,B,C':
SELECT order_id, item
FROM orders, UNNEST(SPLIT(items, ',')) AS item;
This produces one row per item, with the original order_id repeated. Note the comma before UNNEST – that’s an implicit CROSS JOIN, which works because UNNEST is a table function.
Accessing Specific Parts: OFFSET and ORDINAL
If you only need the second element of a split, you can index into the array. BigQuery arrays are 0-based when using OFFSET() and 1-based with ORDINAL().
SPLIT('a,b,c', ',')[OFFSET(1)]→'b'SPLIT('a,b,c', ',')[ORDINAL(2)]→'b'ARRAY_LENGTH(SPLIT('a,b,c', ','))→3
Be careful: accessing an out-of-bounds index returns NULL, not an error, in BigQuery.
When to Use REGEXP_EXTRACT_ALL Instead
SPLIT() only splits on a literal delimiter. If your string has variable whitespace, multiple delimiters, or you need to capture parts that match a pattern, use REGEXP_EXTRACT_ALL(). It returns an array of all substrings that match a regular expression.
Example: extract all numbers from a string 'id: 42, qty: 7':
SELECT REGEXP_EXTRACT_ALL('id: 42, qty: 7', r'\d+') AS numbers;
Result: ['42', '7']. This is far more flexible than splitting on spaces and then filtering.
Handling Nulls and Empty Strings
If the input string is NULL, SPLIT() returns NULL, not an empty array. If you need to treat NULL as an empty array, use IFNULL() or COALESCE():
SELECT COALESCE(SPLIT(NULL, ','), []) AS parts;
Also, trailing delimiters create empty strings. SPLIT('a,b,', ',') returns ['a', 'b', '']. If you want to drop empty parts, you can filter them out with ARRAY and a subquery, but that’s often unnecessary if you understand the behavior.
Common Pitfalls and Performance Tips
- Delimiter case sensitivity:
SPLIT()is case-sensitive. ‘A’ and ‘a’ are different. - Multi-character delimiters:
SPLIT('a--b--c', '--')works fine, but the delimiter must appear exactly. - Performance: Splitting large text columns can be expensive. If you only need the first few parts, consider using
SUBSTR()andINSTR()to extract substrings without building an entire array. - Unnesting large arrays: If you have millions of rows with long strings, the UNNEST operation can create a huge intermediate result. Filter early where possible.
Comparing with Other SQL Dialects
In PostgreSQL, you use string_to_array() and unnest(). In SQL Server, STRING_SPLIT() returns a table directly. In MySQL, there’s no built-in split function until recent versions. BigQuery’s SPLIT() is closest to PostgreSQL’s, but the array indexing and UNNEST syntax are unique to BigQuery (and standard SQL).
Frequently Asked Questions
How do I split a string into columns, not rows?
If you have a fixed number of parts, you can index the array and select each element as a separate column. For example: SELECT parts[OFFSET(0)] AS first, parts[OFFSET(1)] AS second FROM (SELECT SPLIT('a,b', ',') AS parts).
Can I split by multiple different delimiters at once?
No, SPLIT() only takes one delimiter. Use REGEXP_EXTRACT_ALL() with a character class like [;,|] to split on any of several characters.
What is the difference between SPLIT and REGEXP_EXTRACT_ALL?
SPLIT() splits on a literal string and returns all parts, including empty ones. REGEXP_EXTRACT_ALL() returns only the substrings that match a regex pattern, so you can skip unwanted text.
Does SPLIT work with NULL or empty strings?
NULL input returns NULL. Empty string returns an empty array. Trailing delimiters produce empty string elements.
How do I split a string and count the parts?
Use ARRAY_LENGTH(SPLIT(your_string, ',')) to get the number of elements.
Is there a way to split a string into characters?
Yes, use SPLIT(your_string, '') with an empty string as the delimiter. This returns an array of single-character strings.
Related
- Excel Template for Expenses & E-Invoice?
- Excel Template for Annual Calendar: How to Make One
- Excel Budget Report Template: How to Build One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Graphing the Line y = -1: Points, Slope, and ExamplesThe equation y = -1 graphs as a horizontal line one unit below the x-axis. Every point on…
- Create an Email Distribution List in Outlook 365To create an email distribution list in Outlook 365, open People and choose New contact list in new…
- Excel Workbook Statistics: What It Counts and How to Use ItExcel’s Workbook Statistics feature gives you a quick count of items in the active worksheet or the whole…
- Excel Named Range: How to Create and Use OneTo create a named range in Microsoft Excel for Microsoft 365 on Windows, select the cell or range,…
- Excel Power Query Add-In: What It Is and How It WorksIn modern Excel, Power Query is built in, so Microsoft 365 users generally do not need to install…
- Excel Time Shortcut: How to Insert the Current TimeIn Excel for Microsoft 365 on Windows, select a cell and press Ctrl+Shift+; (semicolon) to enter the current…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.