SPLIT

Split the input string by a literal delimiter and return the parts as an array.

Category: stringReturns: ARRAY<VARCHAR>Dialect: Standard

Syntax

SPLIT(str, delimiter)

Description

## Overview Splits the input string at every occurrence of the literal delimiter and returns the parts as an array of strings. The delimiter itself is not included in the output. SPLIT is the array-producing counterpart of SPLIT_PART (which returns a single part by index) and of REGEXP_SPLIT_TO_ARRAY (which uses a regex pattern rather than a literal). Use SPLIT to unpack CSV-like columns, to break a delimited identifier into components, or as the first step in a UNNEST pipeline that turns a packed field into rows. ## Behavior - Returns NULL when either argument is NULL. - Returns a single-element array containing the input string when the delimiter is not found. - Produces empty-string elements when the delimiter appears at the start, end, or consecutively within the input. - The delimiter is treated literally; regex metacharacters are not interpreted. Use REGEXP_SPLIT_TO_ARRAY for pattern-based splitting. - Operates on Unicode code points; multi-byte delimiters and inputs are handled correctly. - An empty delimiter raises an error: the operation is not defined for that case. ## Compatibility - SPLIT returns an ARRAY of STRING. For a set-returning set of rows, use REGEXP_SPLIT_TO_TABLE or UNNEST on the SPLIT result.

Parameters

Name	Type	Description
`str`		Specifies the input string to split.
`delimiter`		Specifies the literal delimiter at which the string is split. Each non-overlapping occurrence starts a new part.

Examples

-- Split on comma
SELECT SPLIT('a,b,c', ',');  -- ['a', 'b', 'c']

-- Split on space
SELECT SPLIT('hello world', ' ');  -- ['hello', 'world']

-- Delimiter absent: one-element array
SELECT SPLIT('hello', ',');  -- ['hello']

-- Adjacent delimiters produce empty-string elements
SELECT SPLIT('a,,b', ',');  -- ['a', '', 'b']

-- NULL propagates
SELECT SPLIT(CAST(NULL AS VARCHAR), ',');  -- NULL

-- Split a tag column and UNNEST into rows
SELECT order_id, t.tag
FROM retail.sales.orders
CROSS JOIN UNNEST(SPLIT(tags_raw, ',')) AS t(tag);

Pitfalls

SPLIT has no limit argument. Pathological inputs with millions of delimiters produce huge arrays. Cap input length or use SPLIT_PART for bounded work.
SPLIT is literal, not regex. SPLIT(s, '.') splits on a literal dot (which is what you usually want); REGEXP_SPLIT_TO_ARRAY(s, '\.') is equivalent, while REGEXP_SPLIT_TO_ARRAY(s, '.') splits on every character.
Adjacent delimiters produce empty-string elements, not skipped elements. Filter them out with ARRAY_FILTER or a post-processing step if you want compact output.
An empty delimiter raises an error. Guard with LENGTH(delimiter) > 0.