In Snowflake SQL, SPLIT(string, delimiter) divides a string wherever a literal delimiter occurs and returns the pieces as an array. Use SPLIT_PART(string, delimiter, position) when you need one particular piece as a string; use FLATTEN with SPLIT when you need each piece on its own row.
Which Snowflake split function should you use?
“Split” can mean several different operations, so choosing the function first helps avoid extra array handling. Snowflake’s main options are:
SPLITcreates an array from a string using a specified delimiter.SPLIT_PARTreturns one field at a specified position without requiring you to work with an array.REGEXP_SPLIT_TO_ARRAYsplits a string wherever a regular-expression pattern matches, and returns an array.FLATTENis a table function that can turn array elements into rows. It does not split text by itself, but it is often paired withSPLIT.
For example, splitting a string of comma-separated labels into an array is a SPLIT task. Getting the second label is usually a SPLIT_PART task. Producing a separate result row for every label calls for SPLIT followed by FLATTEN.
How SPLIT works
The basic syntax is SPLIT(string, delimiter). The delimiter is a literal string, not a regular expression. If the input is 'north|central|south' and the delimiter is '|', the returned array contains three string elements: north, central, and south.
For example, this query returns an array in the result column:
SELECT SPLIT('north|central|south', '|') AS regions;
Snowflake displays the array in a bracketed form in the query results. The result is an array, rather than a single text value with the original separators removed. That distinction matters if you plan to select an element, count pieces, or expand the result into rows.
The delimiter can contain more than one character. For instance, SPLIT('red--blue--green', '--') uses the two-hyphen string as one delimiter. It does not treat each hyphen as an independent separator. A delimiter that does not appear in the input leaves the input as one array element.
Getting one piece with SPLIT_PART
Use SPLIT_PART(string, delimiter, partNumber) when the task is to retrieve a known position. Unlike SPLIT, this function returns a string value rather than an array. Part numbers normally start at 1, so this query returns blue:
SELECT SPLIT_PART('red|blue|green', '|', 2) AS second_color;
A zero part number is treated as 1. A negative number counts backward from the end: SPLIT_PART('red|blue|green', '|', -1) returns green. If the requested position is beyond the available pieces, Snowflake returns an empty string. These behaviors can be useful, but they also mean that an empty result does not necessarily prove the original input was empty; it may indicate a missing field.
For example, if a table called events has a column named event_code containing values such as shop|purchase|mobile, this query selects the middle field:
SELECT event_code, SPLIT_PART(event_code, '|', 2) AS event_type FROM events;
This is often simpler than splitting into an array and indexing it. It also makes the intended position visible in the SQL. If the field order can vary, however, a fixed position may not be a reliable way to identify a value.
Working with SPLIT arrays and indexes
Snowflake arrays are indexed from zero when you access elements by array position. That is different from SPLIT_PART, whose ordinary positive positions start at 1. For instance, the first element of SPLIT('red|blue|green', '|') has array index 0, while the first piece in SPLIT_PART has part number 1.
You can retrieve an array element with GET and cast the result to a string:
SELECT GET(SPLIT('red|blue|green', '|'), 1)::STRING AS second_color;
The index 1 selects the second array element because array indexing starts at zero. GET returns a value of type VARIANT, so the ::STRING cast is useful when a downstream expression or output should be explicitly treated as text. For a known single position, SPLIT_PART is generally more direct.
To count the pieces, apply ARRAY_SIZE to the array. With the text 'red|blue|green' and delimiter '|', ARRAY_SIZE(SPLIT('red|blue|green', '|')) returns 3. Counting can help validate input or identify rows with an unexpected number of fields, although an empty field still counts as an array element.
Turning delimited text into rows with FLATTEN
An array is useful when the pieces belong together in one result value. If you need to filter, group, join, or aggregate individual pieces, expand the array into rows with LATERAL FLATTEN. Suppose a table named products has a tags column containing 'sale,blue,clearance'. This query returns one row per tag:
SELECT products.product_id, f.value::STRING AS tag FROM products, LATERAL FLATTEN(INPUT => SPLIT(products.tags, ',')) AS f;
The value column from FLATTEN contains each array element, and casting it to STRING makes its text type explicit. The function also provides fields such as an array index in its output, which can be selected when the original order matters. For example, selecting f.index alongside the value records whether a tag came first, second, or later in the source string; the index begins at 0.
Because this operation changes the number of rows, it can affect later calculations. A product with four tags produces four output rows. If you join that expanded result to another table or sum a product-level amount afterward, check whether the row multiplication will duplicate the amount. Keeping a stable key such as product_id makes it easier to group the expanded data correctly.
Delimiters, empty fields, and NULL values
Splitting text does not automatically clean or validate it. Repeated delimiters and delimiters at the beginning or end of a string can create empty elements. For example, splitting 'a,,c,' on a comma preserves the empty field between the two commas and the empty field after the final comma. That can be important when positions have meaning: silently removing an empty field would shift the later positions.
Likewise, whitespace is part of the text. Splitting 'red, blue' on a comma produces a second element that begins with a space. The delimiter is only the comma; Snowflake does not automatically trim the pieces. If you need cleaned values, apply a trimming function to the result, or to each value after flattening. For example, in an expanded query, TRIM(f.value::STRING) removes surrounding whitespace from each string element.
NULL should be considered separately from an empty string. A NULL input or NULL delimiter produces a NULL result for SPLIT; an empty field created by adjacent delimiters is not the same thing as NULL. This distinction affects filters, comparisons, and counts. If the source column may be NULL, decide whether a NULL should remain missing, be replaced with a default, or be excluded before splitting. Do not assume that every row will produce at least one usable token.
Literal delimiters are not CSV parsing
A common misconception is that SPLIT(text, ',') is a complete way to parse any comma-separated file. It is not a quote-aware CSV parser. In a value such as red,"blue,light",green, a simple split on commas also breaks at the comma inside the quoted value. The pieces no longer represent the intended three CSV fields.
For a properly formatted CSV file, use Snowflake’s file-format and data-loading features with CSV parsing options rather than treating the whole record as an ordinary delimited string. Those features can account for quoting and other CSV conventions. SPLIT is appropriate for simple, consistently delimited text where the delimiter cannot occur inside a field, or where the data producer has already escaped or otherwise handled that case.
Another related distinction is regular expressions. The pipe character, period, and brackets have special meanings in many regular-expression patterns, but they are ordinary literal characters when passed as the delimiter to SPLIT. If the separator should match a pattern—for example, either a comma or a semicolon—use REGEXP_SPLIT_TO_ARRAY instead:
SELECT REGEXP_SPLIT_TO_ARRAY('red,blue;green', '[,;]') AS colors;
Here the pattern [,;] matches either punctuation mark. Use a regular-expression function only when pattern matching is actually needed; for one fixed delimiter, SPLIT is easier to read.
Choosing a reliable approach
The best choice depends on what the query needs to do and how trustworthy the input format is. Use these checks before building a transformation:
- Choose
SPLIT_PARTfor a specific, known field position, such as the second section of a structured code. - Choose
SPLITwhen you need the entire set of pieces as an array, need its size, or expect to access several elements. - Add
FLATTENwhen each piece needs to become a row for filtering, joining, grouping, or reporting. - Choose
REGEXP_SPLIT_TO_ARRAYwhen the separator is a pattern rather than one fixed literal string. - Use CSV-aware loading for quoted CSV records instead of assuming a simple delimiter split can parse them.
- Check whether fields can be empty, contain whitespace, or be NULL, and define how the query should handle each case.
For recurring analytics, splitting at query time may be convenient, especially when the source is a modest text field and the transformation is used in one place. If many reports repeatedly parse the same field, consider storing or transforming the components into explicit columns during ingestion or in a reusable model. That can make downstream SQL easier to understand and avoids repeating parsing logic. The right design depends on how the data is used and maintained; splitting a string does not by itself guarantee that the underlying data is well structured.
Common mistakes and troubleshooting
One frequent error is using a zero-based position with SPLIT_PART. Its positive positions start at 1, while array indexes used with GET start at 0. Another is expecting SPLIT_PART to return NULL when the requested field does not exist; an out-of-range part returns an empty string, so check for that behavior explicitly when it matters.
Also check that the delimiter matches the stored text exactly. A pipe, a comma followed by a space, and a plain comma are different delimiter strings. With SPLIT(value, ', '), a comma that is not followed by a space will not match that two-character delimiter. To split on a comma regardless of following whitespace, split on the comma and trim the resulting values.
Finally, remember that FLATTEN expands rows. If a query suddenly returns many more rows than its input table, that may be the expected result of expanding arrays rather than a duplicate-data bug. Compare row counts before and after the lateral join, inspect a few source values, and verify that downstream joins use the correct key and intended grain.
Frequently Asked Questions
Does SPLIT return an array or a string?SPLIT returns an array of strings. Use SPLIT_PART when you want one piece returned as a string, or use FLATTEN to return array elements as rows.
Is SPLIT_PART zero-based?
No. Positive part numbers start at 1, so part number 1 means the first piece. Array positions accessed with GET, by contrast, start at 0. A zero SPLIT_PART position is treated as 1.
Can SPLIT use a multi-character delimiter?
Yes. The delimiter is a literal string and can contain multiple characters, such as '--'. Snowflake splits at occurrences of that entire string, not at each individual character in it.
How do I split a string into separate rows?
Use LATERAL FLATTEN(INPUT => SPLIT(column_name, delimiter)). Select the flatten result’s value field for each piece, and cast it to STRING when you need a text value.
Does SPLIT correctly parse quoted CSV fields?
No. A simple split does not honor CSV quotes, so it can split inside a quoted field that contains the delimiter. Use Snowflake’s CSV file-format and loading options for CSV data that may contain quoted delimiters.
Related
- T-SQL STRING_SPLIT: Syntax, Order, and Examples
- JavaScript String Split: Syntax, Examples, and Tips
- JavaScript split(): How to Use It and Avoid Pitfalls
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Example: Step-by-Step Formula Guide for ExcelTo find a product’s price by its ID in Excel, enter =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found") in the result cell. If…
- C++ String Input Functions: cin, getline, and MoreIn C++, use std::cin >> text to read a whitespace-delimited word, and std::getline(std::cin, text) to read a whole…
- How to Know If an Excel File Is Corrupted and What to CheckTo check whether an Excel file is corrupted, first make a copy, then try opening that copy in…
- Range of Log Functions: What It Means and How to Find ItThe range of the basic real logarithmic function f(x) = logb(x) is all real numbers, or (−∞, ∞),…
- Excel Custom Number Format: Round Values to ThousandsTo display Excel values rounded to the nearest thousand without changing the stored numbers, apply the custom number…
- Excel CONCATENATE Formula: How to Join TextIf A2 contains Jane and B2 contains Doe, use =CONCATENATE(A2," ",B2) to join them with a space, producing…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.