Snowflake SPLIT to Array: Syntax, Examples, and Tips

How-to basics · Updated November 22, 2026

Yes. In Snowflake, use SPLIT(text, delimiter) to turn delimited text into an array; for example, SPLIT('red,green,blue', ',') returns an array containing the three strings. The delimiter is matched as literal text, not as a regular expression.

What Snowflake SPLIT returns

SPLIT divides a string wherever the specified separator occurs and returns the pieces as an array. This is useful when a column stores multiple values in one delimited string, such as tags, codes, or a simple list of names. The result is an array value, not a set of separate table rows.

For example, the expression SPLIT('east|central|west', '|') produces an array equivalent to ["east", "central", "west"]. Snowflake can keep that value in a query result or a table column, and other array functions can inspect or transform it. If you need one row per item instead, pair the array with FLATTEN.

Splitting does not interpret the text as CSV. It does not parse quoted fields, remove surrounding spaces, or infer numbers and dates. A piece such as 42 remains a string with its leading spaces; convert or clean values explicitly if the next step requires it.

Syntax and a basic query

The basic syntax is SPLIT(string, separator). Both arguments are strings. The separator can contain more than one character: with SPLIT('one::two::three', '::'), Snowflake uses the entire two-colon sequence as the separator. It does not split separately at each colon.

A complete query might look like this:

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

The result is one row with an array in the COLORS column. To apply it to stored data, pass a text column instead of a literal:

SELECT item_id, SPLIT(color_list, ',') AS colors
FROM inventory;

Here, each source row remains one result row, and COLORS contains that row’s array. If color_list is SQL NULL, the split result is NULL; it is not an array of values. That distinction matters when later counting array elements or expanding them.

Delimiters, empty pieces, and whitespace

SPLIT preserves empty pieces created by separators. For instance, leading, trailing, or repeated separators can create empty strings in the returned array. With a comma-delimited value like ,pear,,plum,, do not assume that the result contains only the two visible fruit names. Empty entries can affect array size, positions, and downstream processing.

The separator must match exactly. Splitting 'a, b, c' on a comma returns pieces with spaces before b and c; splitting on comma-space instead gives different results and will not match a comma that has no following space. Choose the delimiter based on the actual data, including whether spacing varies.

For consistent cleanup, trim each piece after splitting. One way is to expand the array and apply TRIM to each value:

SELECT TRIM(f.value::STRING) AS cleaned_item
FROM source_data AS s,
LATERAL FLATTEN(INPUT => SPLIT(s.item_list, ',')) AS f;

This example also illustrates an important boundary: splitting itself preserves the source content, while trimming is a separate transformation. Decide explicitly whether empty strings should be retained or filtered out; removing them can change the meaning of positional data.

Read one element or count the elements

Snowflake arrays use zero-based positions when accessed by index: the first element is at index 0, the second at 1, and so on. A clear way to retrieve a position is with GET, which returns the array value at the requested index. For a string result, cast that value to STRING.

SELECT GET(SPLIT('red,green,blue', ','), 1)::STRING AS second_color;

This returns green. An index beyond the available elements does not provide a valid item, so account for shorter lists rather than assuming every row has the same number of values. Use ARRAY_SIZE when you need the number of elements:

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

The result is 3. An empty string inside the array still counts as an element, so array size is not always the same as the number of meaningful, nonblank values. If the distinction matters, clean or filter the values and then count the remaining rows.

Turn array elements into rows with FLATTEN

Use the table function FLATTEN when a downstream query needs one record per split value—for example, to join individual tags to a lookup table, group by tag, or count how often each tag appears. A lateral join makes the array available to the function for each input row:

SELECT s.item_id, f.index AS position, f.value::STRING AS tag
FROM source_data AS s,
LATERAL FLATTEN(INPUT => SPLIT(s.tags, ',')) AS f;

For a row whose tags value is new,featured, the query produces two output rows. The INDEX field identifies the zero-based array position, and VALUE holds the element as a semi-structured value; casting it to STRING makes the intended text type explicit. Keeping the source key alongside each value lets you trace the expanded items back to their original row.

Expansion changes the shape and likely the row count of the result. A source row with five entries can produce five rows, while a null or empty array may produce no rows under the default flatten behavior. If you must preserve source rows that have no items, review the OUTER option for FLATTEN and decide how to represent missing values. Avoid expanding an array until you actually need row-level values, since every extra output row can affect later joins and aggregations.

SPLIT compared with related Snowflake functions

Choose the function based on whether you want an array, one selected field, or a different tokenization rule:

  • SPLIT returns all pieces as an array using one exact separator. Use it when you need to keep or process the complete list.
  • SPLIT_PART returns a requested piece rather than an array. It is often simpler when the query needs just one known field, such as the second section of a code.
  • STRTOK_TO_ARRAY is an alternative when any character in a set of delimiter characters should act as a separator. That differs from SPLIT, which treats its separator as a complete literal sequence.
  • Regular-expression functions are appropriate when separators follow a pattern rather than one fixed string. SPLIT itself does not accept a regular expression.

For example, a comma and semicolon can be treated as separate delimiter characters with a tokenizing approach, whereas SPLIT(text, ',;') looks for the exact two-character sequence comma-semicolon. Confusing those behaviors is a common reason a query returns an unexpected single piece. When the intended format is a structured CSV file with quoted commas or escaped quotes, use a proper file format and CSV parsing rather than treating it as plain delimiter-separated text.

Common problems and practical checks

Most unexpected results come from the data format rather than from the function. Before relying on a split result, inspect representative values and check for inconsistent delimiters, extra spaces, empty entries, and nulls. Data imported from different systems may use commas in some rows and semicolons in others, or may include a delimiter inside a value. A simple split cannot know whether that delimiter is intended as a boundary.

  • Only one element appears: confirm that the separator exactly matches the string, including spaces and multi-character sequences.
  • Unexpected blanks appear: look for repeated, leading, or trailing separators, then decide whether to retain or filter empty strings.
  • Values do not match joins: check whitespace and casing, and normalize deliberately with functions such as TRIM or UPPER where appropriate.
  • Some rows disappear after expansion: check for null or empty arrays and whether the default FLATTEN behavior suits the query.
  • A value containing a comma breaks apart: the source may be CSV-like or structured; use a parser that understands its quoting rules instead of plain SPLIT.

Also test boundary cases, not just a typical row. A useful sample includes a null, a single value with no separator, a value with multiple separators, and a value with leading or trailing separators if those can occur. Such checks reveal whether later filters, counts, and joins preserve the intended meaning.

Performance and data-model considerations

For occasional transformations, splitting a text field in a query can be convenient. For repeated analysis, however, storing a changing list in one delimited string can make filtering, validation, and joining harder. A normalized child table—with one row per item and a key back to the parent—may be easier to query and govern when individual list members are important business data.

Splitting and then flattening can multiply rows, so consider the expected list length and what happens to that larger result in subsequent joins. Filter source records early when possible, project only the columns needed, and avoid flattening the same expression repeatedly within a complex query if you can compute and reuse it cleanly. There is no universal performance threshold: warehouse size, data volume, query plan, and later operations all matter. Compare query behavior on representative data rather than assuming that an array is automatically faster or slower than a normalized design.

Frequently Asked Questions

Does Snowflake SPLIT use a regular expression?

No. The second argument is a literal separator string. Use a regular-expression function or another suitable parsing method when the separator is a pattern, not a fixed sequence.

Does SPLIT remove spaces around each value?

No. Spaces are part of the resulting string unless they are included in the separator. Apply TRIM to the pieces when removing surrounding whitespace is correct for your data.

How do I get the first item in a Snowflake split array?

Use index 0, because Snowflake array positions are zero-based. For example, GET(SPLIT('red,green', ','), 0)::STRING returns red.

How can I return each split value as a row?

Pass the split result to FLATTEN, typically with LATERAL FLATTEN(INPUT => SPLIT(column, delimiter)). Select f.value for the item and f.index if the original position matters.

What happens when the source value is NULL?

A SQL NULL input produces a NULL split result, not a populated array. In a query that uses FLATTEN, account for such rows if they must remain in the output.

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.