Snowflake SPLIT Syntax: Arrays, Examples, and Tips

How-to basics · Updated November 22, 2026

In Snowflake, use SPLIT(string, separator) to divide a string wherever a literal separator occurs; the function returns an array of strings, not separate table rows. For example, SPLIT('north|south|west', '|') returns an array containing north, south, and west.

What Snowflake SPLIT Returns

SPLIT is a string function that produces an array. That distinction matters: a query that selects the function still returns one result row for each input row, with the pieces held together in one array value. To turn the pieces into individual rows, use the table function FLATTEN, described below.

The basic syntax is:

SPLIT(<string_expression>, <separator>)

The separator is treated as a literal string, not a regular expression. For instance, splitting on a period uses '.' as the separator; it does not mean “any character,” as a period commonly does in regular-expression syntax. The separator can also contain more than one character, such as '::'.

A simple query makes the return type visible:

SELECT SPLIT('north|south|west', '|') AS regions;

Snowflake displays the result as an array, typically in a form like ["north","south","west"]. Each element is a string. You can keep the array as a value, select individual elements, or expand it into rows depending on what the next step in your query requires.

Basic Syntax and Practical Examples

Use the separator that actually appears in the source value. A pipe-delimited field, for example, can be split like this:

SELECT SPLIT('sku-104|blue|large', '|') AS fields;

The result is a three-element array: sku-104, blue, and large. A multi-character separator works the same way:

SELECT SPLIT('west::central::east', '::') AS zones;

This produces three elements, split at each occurrence of the two-character sequence ::. Snowflake does not interpret the separator as a pattern, so a separator such as '||' means two literal pipe characters, not a regular-expression operator.

You can also use SPLIT directly in a query over a table. If a table named events has a column named tag_list, this returns an array for every row:

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

Here, each row retains its original event_id; its tags are packaged in the array column. If a value should be divided into rows for filtering or joining, expand it rather than expecting SPLIT to change the number of rows by itself.

Getting One Piece with SPLIT_PART

If you need just one component of a delimited string, SPLIT_PART is often more direct than building an array and extracting an element. Its syntax is SPLIT_PART(string, delimiter, part_number). The part number is one-based: 1 means the first part, 2 the second, and so on.

For example, with a host name in mail.example.net, the second component can be selected using SPLIT_PART('mail.example.net', '.', 2), which returns example. A negative part number counts from the end, so SPLIT_PART('mail.example.net', '.', -1) returns net. A part number of zero is treated as the first part. If the requested position does not exist, the function returns an empty string.

Choose SPLIT_PART when the desired position is fixed and you only need one value. Choose SPLIT when you need the complete set of pieces, want to work with an array, or plan to expand the pieces into rows. That choice can make a query easier to read and avoids extracting an array element when it is not needed.

Accessing Array Elements and Counting Them

Snowflake array positions start at zero when using the array accessor. For example, the first element of a split result is at index 0, the second at index 1. You can retrieve an element with GET and cast it to a string:

SELECT GET(SPLIT('red|blue|green', '|'), 0)::STRING AS first_color;

This returns red. For the third item, use index 2. This zero-based array indexing differs from the one-based part numbering of SPLIT_PART, a frequent source of off-by-one mistakes.

To count how many elements an array contains, use ARRAY_SIZE. With a value of 'red|blue|green' in the expression, ARRAY_SIZE(SPLIT('red|blue|green', '|')) returns 3. The count tells you the number of array elements; it does not guarantee that every element contains meaningful text, because empty strings can be elements too.

Expanding Split Values into Rows with FLATTEN

To make one row per token, apply FLATTEN to the array returned by SPLIT. For a table named products with columns product_id and colors, a typical pattern is:

SELECT p.product_id, f.index, f.value::STRING AS color
FROM products AS p,
LATERAL FLATTEN(INPUT => SPLIT(p.colors, ',')) AS f;

FLATTEN returns a row for each array element. Its index field gives the element position, starting at zero, while value contains the element as a semi-structured value. The cast ::STRING makes the intended text type explicit for later comparisons or output.

For the input red,blue, the expanded result has two rows for that product: one with index 0 and value red, and another with index 1 and value blue. The lateral relationship lets the table function use the current row’s colors value. This pattern is useful when tokens need to be filtered, grouped, joined to a lookup table, or counted across records.

By default, a flatten operation does not preserve a source row when its input produces no elements. If retaining such rows is important, review the OUTER option for FLATTEN and test the behavior with the actual null and empty values in your data.

Empty Values, Whitespace, and NULLs

Splitting does not automatically clean or normalize the text. If the input is 'red, blue', the second element includes its leading space. Trim values when appropriate, for example by applying TRIM after expansion: TRIM(f.value::STRING). Do not trim blindly if whitespace is meaningful to the data.

Repeated separators can create empty elements. For example, SPLIT('red,,blue', ',') includes an empty string between red and blue. Separators at the start or end of a value can likewise leave empty pieces. These are not the same as a missing array element, so decide whether empty tokens should be retained, filtered, or treated as a data-quality issue.

A NULL input produces a NULL result, rather than a usable array of text values. This can affect later operations: a query that assumes every input can be flattened may behave differently for nulls than for ordinary delimited strings. Check for NULL and empty source values when designing a transformation, especially if the result feeds a join or a count.

When SPLIT Is Not the Right Parser

SPLIT is suitable when the data follows a simple, consistent delimiter convention. It is not a full parser for CSV or other quoted formats. For example, a comma inside a quoted field may be data rather than a field boundary; a basic split on commas cannot reliably distinguish the two. If the source is a CSV file, use Snowflake’s file-format and data-loading options to parse CSV structure instead of splitting a raw line yourself.

Likewise, do not use SPLIT when the boundary is defined by a changing pattern rather than a fixed literal separator. Snowflake provides regular-expression functions, including REGEXP_SPLIT_TO_ARRAY, for cases that require a regex pattern. Regex syntax has its own escaping rules, so verify the pattern separately and choose the simpler literal split whenever it meets the requirement.

For structured JSON data, parse and query the JSON structure rather than treating serialized JSON as a delimited string. The right tool depends on what the source represents: literal splitting for simple separators, a regex split for pattern-defined boundaries, and format-aware parsing for structured input.

Common Mistakes and Query Design Tips

  • Expecting rows from SPLIT: It returns one array value. Use LATERAL FLATTEN when each token must become a row.
  • Mixing up indexes: Array access and FLATTEN‘s index start at zero, while SPLIT_PART uses one-based numbering.
  • Assuming regular expressions: The separator passed to SPLIT is literal. Use a regex split function when boundaries are pattern-based.
  • Ignoring spaces and empty tokens: Inspect representative source values and decide whether to trim or filter pieces explicitly.
  • Splitting quoted CSV by hand: A delimiter can occur inside a quoted field. Use CSV-aware loading rather than a simple split for that format.

For large tables, consider the amount of data expanded by flattening. A row with many tokens becomes many result rows, which can increase downstream processing. Filter source rows before expansion when that is logically valid, and select only the columns needed for the operation. These are general query-design considerations, not a reason to avoid SPLIT when an array is the right representation.

Frequently Asked Questions

Does Snowflake SPLIT return an array?
Yes. SPLIT(string, separator) returns an array of strings. Use FLATTEN if you need one result row per element.

Is the separator in SPLIT a regular expression?
No. It is a literal separator string. For pattern-based splitting, use a regular-expression function such as REGEXP_SPLIT_TO_ARRAY.

How do I get the first item from SPLIT?
Use GET(SPLIT(value, delimiter), 0)::STRING to retrieve the first array element. The array index is zero-based.

What is the difference between SPLIT and SPLIT_PART?
SPLIT returns all pieces in an array. SPLIT_PART returns one selected piece using a one-based part number, with negative numbers counting from the end.

Why does my split result contain blank items?
Repeated or leading or trailing separators can produce empty-string elements. Check the source text and decide whether those values should be preserved or removed.

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.