Snowflake SPLIT: Turn a String Into an Array

How-to basics · Updated November 22, 2026

Yes. In Snowflake, use SPLIT(string, delimiter) to divide a string at each occurrence of a delimiter and return the pieces as an array. For example, SPLIT('red,green,blue', ',') returns an array containing red, green, and blue.

What SPLIT Returns

SPLIT is a built-in Snowflake SQL function. Its two arguments are the input string and the separator to look for. The separator is treated as a literal string, not as a regular expression, and it can contain more than one character. The return value is an array, which is useful when you want to keep the pieces together for further SQL operations rather than immediately turn them into separate rows.

For example, this query creates an array from a comma-separated value:

SELECT SPLIT('red,green,blue', ',') AS colors;

The result is an array with three string elements in their original order. Snowflake’s results display may show the array in a JSON-like form, but the function returns an array value; it does not simply return one long string with commas in it. A delimiter that does not occur in the input leaves the input as one array element.

Basic Syntax and Practical Examples

The general form is SPLIT(<string>, <separator>). Both arguments are expressions, so you can use a column for the string and a fixed separator, or provide expressions for both. Here are common patterns:

  • SPLIT('north|south|west', '|') splits a pipe-delimited value.
  • SPLIT('2026-11-03', '-') returns the date components as strings; it does not convert them into date or number values.
  • SPLIT('one::two::three', '::') uses the full two-character separator ::, not either colon independently.
  • SPLIT(product_codes, ',') applies the split to a column named product_codes.

In a query over a table, the expression might look like this:

SELECT order_id, SPLIT(item_list, ',') AS items
FROM orders;

Each result row contains the original order identifier and an array for that row’s item list. The function does not automatically trim spaces, change letter case, validate the pieces, or cast them to another data type. Those are separate tasks.

Separators, Empty Pieces, and Whitespace

Snowflake splits wherever it finds the exact separator string. A separator can therefore be a comma, a pipe, a space, or a multi-character sequence. For instance, splitting 'a--b--c' on '--' produces three pieces. This is not the same as interpreting the separator as a pattern: a period or a plus sign is just that literal character.

Adjacent separators can produce empty elements. For example, splitting 'a,,b' on a comma preserves the empty piece between the two commas. Separators at the start or end of a string can likewise leave an empty piece at the corresponding edge. This behavior matters when empty fields have meaning, such as a missing middle value in a delimited record. Do not assume that SPLIT silently filters blanks.

Whitespace is also preserved. Splitting 'red, green, blue' on a comma leaves a leading space on the second and third elements. If you need to clean the values while expanding them into rows, trim each value in the query:

SELECT TRIM(f.value::STRING) AS color
FROM LATERAL FLATTEN(INPUT => SPLIT('red, green, blue', ',')) AS f;

That expression trims spaces at the edges of each element. It does not remove spaces inside an element, and it does not change the original string stored in a table.

NULL Values and Edge Cases

As with many Snowflake string functions, a NULL input string or a NULL separator produces NULL rather than an array of tokens. That is different from an empty string, which is a string value and should not be treated as interchangeable with NULL. If your source column can be missing, decide whether a NULL array is the desired result or whether you need to handle it explicitly with a conditional expression such as COALESCE.

An empty separator is another special case: it does not mean split the input into individual characters. Snowflake returns the original string as a single array element. If your goal is character-level processing, use a method designed for that task rather than expecting SPLIT with an empty separator to do it.

It is also useful to distinguish a separator that is absent from one that appears once. When the separator is absent, there is no boundary to divide on, so the array contains the original text as one piece. When it appears, the result reflects the pieces on either side, including empty pieces when boundaries are adjacent. These details can affect counts and downstream joins, so test representative inputs from your data.

Get Array Elements or Count Them

Once SPLIT returns an array, use Snowflake’s array operations to inspect it. Snowflake array indexing is zero-based when you use bracket notation, so the first element is at index 0, the second at 1, and so on. For example:

SELECT SPLIT('red,green,blue', ',')[0] AS first_color;

This selects the first array element. The returned array values are string-like elements within Snowflake’s semi-structured array representation; cast explicitly when you need a numeric or other specific SQL type. For example, if a comma-separated string contains numbers, splitting it does not itself make those values numeric.

To count the elements, wrap the expression in ARRAY_SIZE:

SELECT ARRAY_SIZE(SPLIT('red,green,blue', ',')) AS item_count;

For this example, the count is 3. Empty elements count as elements too, so an input with adjacent delimiters may have a larger array size than the number of non-empty values. If you need a count of meaningful values, first define what should count as meaningful and filter or clean the elements accordingly.

Turn Array Elements into Rows

A frequent follow-up is not just to create an array but to get one row per item. Use the table function FLATTEN with the array returned by SPLIT. Its VALUE column holds each element, and casting it to STRING makes the intended type explicit:

SELECT f.value::STRING AS item
FROM LATERAL FLATTEN(INPUT => SPLIT('pen,paper,folder', ',')) AS f;

This produces three rows, one for each item. With a table column as input, include the source table and a lateral flatten in the FROM clause:

SELECT o.order_id, f.index, f.value::STRING AS item
FROM orders AS o,
LATERAL FLATTEN(INPUT => SPLIT(o.item_list, ',')) AS f
ORDER BY o.order_id, f.index;

The index from FLATTEN identifies each element’s position in the array. Although the array has an order, SQL query results are not guaranteed to appear in a particular order unless you specify ORDER BY. Ordering by the source row and the flattened index makes the intended sequence clear. If the source column is NULL, consider how you want that row handled; an inner lateral expansion may not preserve a parent row that produces no flattened elements.

SPLIT Versus SPLIT_TO_TABLE and STRTOK_TO_ARRAY

Choose the function that matches the shape of the result you need. SPLIT returns an array in a single row. SPLIT_TO_TABLE is a table function that returns the separated values as rows, which can be convenient when row-wise processing is the goal from the start. For example:

SELECT value
FROM TABLE(SPLIT_TO_TABLE('pen,paper,folder', ','));

SPLIT_TO_TABLE returns columns including a sequence identifier, an index, and the value. Its index is useful for retaining token position. As with other table results, add an ORDER BY when display order matters. You do not need to create an array and then flatten it when the table-shaped result is the only thing you want.

STRTOK_TO_ARRAY is different from SPLIT: it treats its delimiter argument as a set of delimiter characters rather than one exact multi-character separator. It is useful for tokenizing on any of several characters, but it is not a drop-in replacement when repeated separators or empty fields must be preserved. In short, use SPLIT for an exact separator and an array, SPLIT_TO_TABLE for an exact separator and rows, and STRTOK_TO_ARRAY when character-based tokenization fits the input.

When SPLIT Is Not the Right Tool

SPLIT is suitable for simple, consistently delimited text. It is not a full CSV parser. A CSV field can contain a comma inside quoted text, as in "Smith, Ana",42; splitting that line on commas would incorrectly break the name into two pieces. For CSV files, use Snowflake’s file-format and data-loading features so quoting, escaping, and record boundaries are interpreted according to the format.

Similarly, splitting a string that contains escaped separators requires understanding the escape rules first. A plain SPLIT does not know that a particular comma is protected by a backslash or quotation mark. If delimiters can appear inside values, use a parser suited to the data format, or normalize the data at its source. Choosing a simplistic split can create plausible-looking but incorrect records.

Also remember that SPLIT changes structure, not meaning. It does not validate email addresses, dates, identifiers, or numeric values. Validate or cast the resulting elements separately, and handle values that fail those operations according to the needs of your query.

Frequently Asked Questions

Does Snowflake SPLIT use a regular expression?
No. The separator is a literal string. For example, '.' means a period, not a regular-expression wildcard, and a multi-character separator is matched as that full string.

How do I split a column in Snowflake?
Pass the column name as the first argument and the delimiter as the second, such as SPLIT(tags, ','). Use the result directly as an array, or pass it to FLATTEN if you want one row per element.

How do I get the first value after splitting?
Use zero-based array indexing. For example, SPLIT('a,b,c', ',')[0] returns the first element, a.

Does SPLIT remove spaces or empty values?
No. It preserves whitespace around pieces, and adjacent or edge separators can produce empty elements. Apply trimming or filtering separately if your data rules call for it.

How can I split a string into rows instead of an array?
Use SPLIT_TO_TABLE for a direct table result, or use FLATTEN(INPUT => SPLIT(...)). Choose FLATTEN when you also want to work with the array or its element index.

What happens if the input or separator is NULL?
SPLIT returns NULL if either argument is NULL. If you need a different behavior, handle NULL explicitly before or around the function.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.