SPLIT Function in BigQuery: Syntax & Examples

Formulas & functions · Updated November 22, 2026

The SPLIT function in BigQuery splits a string into an array of substrings based on a delimiter. For example, SPLIT('a,b,c', ',') returns ['a', 'b', 'c']. It is a simple, powerful way to break apart delimited text directly in SQL.

What Is SPLIT and Why Use It?

BigQuery’s SPLIT is a scalar function that takes a STRING (or BYTES) input and a delimiter, and returns an ARRAY<STRING> (or ARRAY<BYTES>). It is part of the standard SQL dialect that BigQuery supports. You might use it to parse comma-separated values, extract tags from a text field, or break a full name into parts. Unlike some other SQL dialects that have a STRING_SPLIT table function, BigQuery’s SPLIT returns an array, which you can then flatten with UNNEST if you need one row per element.

Syntax and Basic Usage

The syntax is straightforward:

SPLIT(value[, delimiter])
  • value: the string or bytes expression to split.
  • delimiter: optional, defaults to a comma (,). If omitted, it splits on every comma.

Example: SELECT SPLIT('apple,banana,cherry') AS fruits; returns ['apple', 'banana', 'cherry'].

If you provide a delimiter, it must be a string. For instance, SPLIT('2024-01-15', '-') returns ['2024', '01', '15'].

One common pattern is to use SPLIT with UNNEST to turn an array into rows:

SELECT fruit
FROM UNNEST(SPLIT('apple,banana,cherry')) AS fruit;

This returns three rows, one for each fruit.

Delimiter Behavior and Edge Cases

The delimiter is treated as a literal string, not a regular expression. So SPLIT('a.b.c', '.') splits on the period character, not any wildcard. If the delimiter appears at the start or end of the string, the result includes empty strings. For example, SPLIT(',a,', ',') returns ['', 'a', '']. Consecutive delimiters produce empty elements: SPLIT('a,,b', ',') returns ['a', '', 'b'].

If the delimiter is an empty string (''), BigQuery splits the string into individual characters. So SPLIT('hello', '') returns ['h', 'e', 'l', 'l', 'o'].

If the input string is NULL, the result is NULL. If the delimiter is NULL, the function returns NULL as well.

Practical Examples with Real Data

Suppose you have a table events with a column tags containing comma-separated tags like 'sale,summer,2024'.

To count how many tags each event has:

SELECT event_id, ARRAY_LENGTH(SPLIT(tags, ',')) AS tag_count
FROM events;

To find all events that have the tag ‘sale’:

SELECT event_id
FROM events
WHERE 'sale' IN UNNEST(SPLIT(tags, ','));

To expand tags into a single column with one row per tag:

SELECT event_id, tag
FROM events, UNNEST(SPLIT(tags, ',')) AS tag;

Note: using a comma after events is shorthand for a CROSS JOIN with the array.

Common Errors and How to Avoid Them

  • Expecting a table, getting an array: SPLIT returns an array, not a set of rows. Use UNNEST to flatten.
  • Using a regex delimiter: If you need to split on a pattern like multiple spaces, SPLIT won’t work because it treats the delimiter literally. Use REGEXP_EXTRACT_ALL or REGEXP_REPLACE first, or use SPLIT with a specific delimiter after normalizing.
  • Ignoring empty strings: Empty elements can cause issues in downstream logic. Use ARRAY_FILTER to remove them if needed.
  • Delimiter with special characters: Since the delimiter is literal, a backslash or a quote is fine, but remember to escape quotes in SQL string literals.

Alternatives and Related Functions

BigQuery offers several functions that work with strings and arrays:

  • REGEXP_EXTRACT_ALL: extracts all substrings matching a regular expression, which can be used for more complex splitting.
  • SPLIT vs REGEXP_SPLIT: BigQuery does not have a REGEXP_SPLIT function; use REGEXP_EXTRACT_ALL or REGEXP_REPLACE with SPLIT.
  • ARRAY_TO_STRING: the inverse of SPLIT, joins array elements into a single string.
  • UNNEST: converts an array into a set of rows, often used with SPLIT.
  • STRING_AGG: aggregates values into a single string, often with a delimiter.

If you come from SQL Server, note that STRING_SPLIT returns a table, not an array. In BigQuery, you emulate that with UNNEST(SPLIT(...)).

Performance and Cost Considerations

SPLIT is a scalar function and is generally fast. However, using it in a WHERE clause on large tables can be expensive because it prevents partition pruning and may require scanning all rows. For example, WHERE 'sale' IN UNNEST(SPLIT(tags, ',')) forces BigQuery to process every row’s tags field. If you frequently filter by tag, consider storing tags in a separate table or using a repeated column (ARRAY) directly, which is more efficient.

Also, splitting large strings can increase the number of output rows dramatically, which can affect query cost (BigQuery charges by bytes processed, not by rows, but more rows may mean more shuffle). Use ARRAY_LENGTH to check the size before expanding.

Frequently Asked Questions

Can I split on multiple different delimiters at once? No, SPLIT only accepts a single delimiter string. To split on multiple characters, you can first replace them with a common delimiter using REPLACE or REGEXP_REPLACE, then call SPLIT. For example, to split on commas or semicolons: SPLIT(REGEXP_REPLACE('a;b,c', '[;,]', ','), ',').

What is the difference between SPLIT and REGEXP_EXTRACT_ALL? SPLIT uses a literal delimiter and returns all pieces, including empty ones. REGEXP_EXTRACT_ALL uses a regex pattern and only returns matching substrings, so empty strings are not included. For example, REGEXP_EXTRACT_ALL('a1b2c3', r'\d') returns [‘1′,’2′,’3’].

How do I split a string into characters? Use an empty string as the delimiter: SPLIT('hello', '') returns [‘h’,’e’,’l’,’l’,’o’].

Does SPLIT work with NULL values? If the input string is NULL, the result is NULL. If the delimiter is NULL, the result is NULL. There is no option to ignore NULLs; you must handle them with IFNULL or COALESCE if needed.

Can I use SPLIT in a GROUP BY clause? Yes, but you need to unnest first. For example, to count occurrences of each tag across all events: SELECT tag, COUNT(*) FROM events, UNNEST(SPLIT(tags, ',')) AS tag GROUP BY tag;

Is there a limit to the number of elements SPLIT can return? BigQuery does not document a specific limit for array size, but practical limits are tied to query memory and the 10 MB maximum for a single value. Extremely long strings may hit that limit.

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.