Snowflake SQL SPLIT: Syntax, Examples, and Alternatives

How-to basics · Updated November 22, 2026

In Snowflake SQL, use SPLIT(string, delimiter) to divide text wherever an exact delimiter occurs; the function returns an array of string values. For example, SPLIT('red,green,blue', ',') returns an array containing red, green, and blue. Use SPLIT_TO_TABLE instead when you need one output row per piece.

What Snowflake SPLIT does

SPLIT is useful when a column contains several values joined into one string, such as comma-separated tags, pipe-separated codes, or a path separated by slashes. It searches for the delimiter you provide and separates the input at each match. The delimiter is literal text, not a regular expression: splitting on ||, for example, looks for two consecutive pipe characters.

The result is an array, rather than a set of rows or a single selected value. That distinction determines what to do next. You can keep the array as an array, select one position, or expand its elements into rows with a table function. SPLIT changes how the string is represented in the query result; it does not update or rewrite the original table column.

Basic syntax and a simple example

The general form is SPLIT(string, separator). Both arguments are expressions that evaluate to strings. Here is a minimal query:

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

The result is one row with an array containing three values: north, south, and west. To split a table column, substitute the column name for the string literal:

SELECT customer_id, SPLIT(interests, ';') AS interest_array
FROM customers;

If interests contains travel;gardening;music, the corresponding array has those three elements. The query still returns one row for that customer. This is often a good format for further array operations or for returning structured data to an application.

Delimiters, spaces, and repeated separators

Choose the separator that actually appears in the source text. A comma and a comma followed by a space are different delimiter strings. With SPLIT('red, green', ','), the second array element retains its leading space: green. The function does not automatically trim whitespace from each value.

For a simple comma-separated field, a common approach is to split on the comma and trim each element while expanding the array:

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

This produces one row per item with surrounding spaces removed. Repeated separators can create empty elements between values, and a separator at the beginning or end can leave an empty element at the corresponding edge. For instance, a value such as a,,b has an empty piece between the two commas. Do not assume SPLIT silently discards such pieces; decide whether empty values are meaningful and handle them explicitly if they are not.

Reading individual pieces with SPLIT_PART

If the goal is to retrieve one piece rather than an array, SPLIT_PART(string, delimiter, partNumber) is often simpler. Its part number is one-based: 1 selects the first piece, 2 the second, and a negative number counts from the end. For example:

SELECT SPLIT_PART('dept/finance/reports', '/', 2) AS department;

This returns finance. SPLIT_PART returns a string value, so it is convenient for extracting a known component such as a file extension or a section of a fixed-format code. If the component position varies, or if all the pieces are needed, use SPLIT and array operations or expand the pieces into rows instead. A fixed part number also does not validate that the source string follows the expected format.

Turning a delimited string into rows

Use SPLIT_TO_TABLE when each separated value should become its own result row. It is a table function, so it belongs in the FROM clause and is commonly joined to the source row with LATERAL:

SELECT o.order_id, s.index, s.value
FROM orders AS o,
LATERAL SPLIT_TO_TABLE(o.product_codes, ',') AS s
ORDER BY o.order_id, s.index;

The function provides SEQ, INDEX, and VALUE columns. VALUE contains a split piece, while INDEX identifies its position and starts at 1. Ordering by the source key and index makes the displayed rows follow the original sequence. Do not treat SEQ as a guaranteed, gap-free row number or as a substitute for an explicit ordering rule.

Another option is to use FLATTEN on the array returned by SPLIT. That is useful when the query also needs array-oriented operations. A key difference is that FLATTEN‘s INDEX is zero-based, whereas SPLIT_TO_TABLE‘s INDEX starts at 1. Choose one approach deliberately, especially if you expose the position in a report or join on it.

When the separator is a pattern

Because SPLIT treats its separator literally, it is not the right function when any one of several characters can mark a boundary. For pattern-based splitting, Snowflake provides REGEXP_SPLIT_TO_ARRAY. For example, this splits on either a comma or a pipe:

SELECT REGEXP_SPLIT_TO_ARRAY('red,blue|green', '[,|]') AS parts;

The second argument is a regular expression; in this example the character class matches either delimiter. Pattern syntax brings more flexibility but also more room for mistakes: regular-expression characters can have special meanings, so escape them when you intend to match them literally. If the data has one consistent separator, ordinary SPLIT is usually clearer.

NULLs, empty input, and data quality

Check for missing or malformed input before relying on a split result. If the input string or delimiter is NULL, SPLIT returns NULL rather than a populated array. Empty strings and empty separators can also have special or surprising behavior, so avoid relying on them as a way to express “split into characters” or “no delimiter.” Define the desired treatment and verify it with representative values in Snowflake before using it in a production transformation.

Delimited text is not necessarily a dependable data format. A comma inside a quoted name, for example, may be data rather than a boundary; a basic SPLIT does not parse CSV quoting rules. If values can contain the delimiter, use a proper structured or CSV-aware ingestion process instead of assuming a simple string split will preserve meaning. Also consider filtering empty pieces, trimming whitespace, and checking the expected number of components before using the results in joins or reports.

Choosing the right Snowflake function

  • Use SPLIT when you need an array of all pieces and the separator is a fixed string.
  • Use SPLIT_PART when you need one known position and want a string result.
  • Use SPLIT_TO_TABLE when the pieces need to become rows, with a one-based position available.
  • Use FLATTEN with SPLIT when you need to expand an array as part of other array-oriented query logic; remember its zero-based index.
  • Use REGEXP_SPLIT_TO_ARRAY when the boundary is described by a regular expression rather than one exact delimiter.

The right choice depends on the shape the next step needs, not only on which function has the shortest syntax. Arrays keep related values together in one row; table functions make individual values easier to filter, group, join, or count. If a field is frequently queried as separate values, consider storing or modeling those values in a more structured form rather than repeatedly parsing a packed string.

Common mistakes to avoid

  • Expecting rows from SPLIT: SPLIT returns an array in the current row. Use SPLIT_TO_TABLE or FLATTEN to produce rows.
  • Forgetting whitespace: splitting on a comma does not remove spaces after commas. Apply TRIM where appropriate.
  • Using a regex with SPLIT: SPLIT matches literal separator text. Use REGEXP_SPLIT_TO_ARRAY for pattern matching.
  • Assuming positions start at the same value: SPLIT_TO_TABLE uses a one-based INDEX; FLATTEN uses a zero-based INDEX.
  • Assuming output order: add an ORDER BY using the source row key and the returned position when sequence matters.
  • Treating simple splitting as CSV parsing: quoted delimiters and escaping require a parser or a better structured input format.

Frequently Asked Questions

Does Snowflake SPLIT return an array?
Yes. SPLIT(string, delimiter) returns an array of the string pieces. It does not create extra result rows by itself.

How do I split a string into rows in Snowflake?
Use SPLIT_TO_TABLE(string, delimiter) in the FROM clause, usually with LATERAL for a table column. You can also pass SPLIT(column, delimiter) to FLATTEN.

How do I split on more than one delimiter?
Use REGEXP_SPLIT_TO_ARRAY with a regular expression that matches the possible separators. For an exact, single separator, use SPLIT instead.

How do I get the second value after splitting?
Use SPLIT_PART(text, delimiter, 2) when you need the second piece as a string. If you need the full array, select its second element using Snowflake’s zero-based array indexing.

Why does my split result contain spaces or blank values?
SPLIT preserves text around separators, including spaces, and repeated or edge separators can produce empty pieces. Apply trimming or filter unwanted empty values only after deciding whether they are valid in your data.

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.