Snowflake’s SPLIT function divides a string wherever an exact separator occurs and returns the pieces as an array. Use SPLIT(string, separator); for example, SPLIT('red,green,blue', ',') returns an array containing red, green, and blue.
What SPLIT Returns
SPLIT is useful when a value contains several parts in one text field and you need to work with those parts separately. A comma-separated list, a pipe-delimited code, or a path separated by slashes are typical examples. Unlike a function that returns one selected piece, SPLIT returns all pieces together in an array.
The function’s basic syntax is:
SPLIT(<string>, <separator>)
Both arguments are strings. The separator is a literal string, not a regular expression or a set of alternative characters. For instance, splitting on '--' looks for two consecutive hyphens as one delimiter; it does not mean “either hyphen.” The result is an ARRAY value, which you can inspect, index, or expand into rows using other Snowflake features.
A simple query demonstrates the output:
SELECT SPLIT('north|south|west', '|') AS parts;
The result is one row with an array containing the three strings. The function does not automatically create three SQL rows or three columns. That distinction matters when planning the next step: use array indexing to select an element, or use FLATTEN when you need a row for each element.
Syntax and Basic Examples
Here are common ways to call the function in a query:
SPLIT('2026-11-03', '-')separates the date text at each hyphen.SPLIT('sku:blue:large', ':')separates a colon-delimited code into its components.SPLIT(file_path, '/')separates a path stored in a column namedfile_path.
For a table column, use the column expression as the first argument. With a table named orders and a column named tags, for example:
SELECT tags, SPLIT(tags, ',') AS tag_array FROM orders;
Each input row remains one output row, and tag_array holds that row’s split values. If the column is not already a string, explicitly convert it to a suitable text representation before splitting. The delimiter must match the characters actually present in the data, including their spacing and punctuation.
The separator can contain more than one character. If a field uses the string ' | ' as its delimiter, including spaces around a vertical bar, splitting on just '|' removes the bar but leaves spaces in the neighboring pieces. Splitting on the full delimiter can produce cleaner values, provided the data uses that exact pattern consistently.
How Delimiters and Empty Values Behave
SPLIT looks for each occurrence of the entire separator string. Repeated separators can therefore create empty elements. For example, splitting 'a||b' on '|' yields three elements: a, an empty string, and b. A separator at the beginning or end of the input also leaves an empty element at the corresponding edge. These empty values are not the same as SQL NULL.
This behavior is important with imperfect or inconsistent source data. Suppose a list is written as 'red,,blue'. The middle item is empty because two commas occur consecutively. If you count the array elements, that empty item is still part of the result. Do not assume that splitting removes repeated delimiters or filters out blank pieces.
A separator consisting of an empty string is a special case: Snowflake returns the input string as a single array element rather than separating it into individual characters. If the separator itself is SQL NULL, or the input string is NULL, the result is NULL. A NULL result is different from an array containing an empty string, so account for that distinction in downstream logic.
Whitespace is preserved. Splitting 'red, green' on a comma returns a second value with a leading space; SPLIT does not trim either side of the delimiter. If whitespace is unwanted, apply a trimming function to the individual values or clean the input before splitting. The right choice depends on whether spaces could be meaningful in the source.
Accessing an Element in the Array
Snowflake arrays use zero-based positions when accessed by index: the first element is at index 0, the second at index 1, and so on. You can use bracket notation on the split result:
SELECT SPLIT('one,two,three', ',')[0] AS first_item;
You can also use GET to retrieve an element. A representative query is GET(SPLIT('one,two,three', ','), 1), which selects the second item, two. Array access is useful when the format guarantees a fixed position, such as a two-part code where the first component always has the same meaning.
Keep the zero-based indexing rule separate from the numbering used by SPLIT_PART, which is a related function discussed below. When you need a text value for comparison or display, cast an extracted array value to a string as appropriate, for example with ::STRING. Also consider short or malformed inputs: a requested position may not exist, so queries should not rely on every row having the expected number of pieces unless that condition is enforced or checked.
Turning Array Elements into Rows with FLATTEN
To produce one row per split value, combine SPLIT with Snowflake’s FLATTEN table function. For a table named products with a comma-delimited categories column, a lateral join can expand the array:
SELECT p.product_id, f.index, f.value::STRING AS category FROM products AS p, LATERAL FLATTEN(INPUT => SPLIT(p.categories, ',')) AS f;
This produces a row for each array element. The flattened output includes fields such as INDEX and VALUE; here, the index shows the element’s zero-based position, and casting VALUE to STRING gives a convenient text column. A product with three categories can therefore appear in three result rows.
Choose row expansion only when it suits the task. It is helpful for filtering individual tags, joining each value to a reference table, grouping or counting values, and loading normalized records. If the goal is only to display the original list or retrieve its first component, flattening adds unnecessary rows and complexity. Remember that a NULL input or an empty array may not produce the rows you expect; verify the behavior against your data and the lateral join you use.
SPLIT, SPLIT_PART, and SPLIT_TO_TABLE
Snowflake offers related tools for different output shapes. Use SPLIT when you need an array containing all parts. Use SPLIT_PART when you want one specific part as a string. Its position argument is one-based: position 1 means the first part. For example, SPLIT_PART('one,two,three', ',', 2) returns two, whereas array index 1 after SPLIT also refers to the second part but follows zero-based indexing.
Use SPLIT_TO_TABLE when the desired result is a table of split values rather than an array you plan to manipulate. It is a table function that returns rows for the pieces, along with positional and sequence information. This can be convenient for direct row-oriented processing. FLATTEN is more general because it expands arrays and other supported semi-structured values, while SPLIT_TO_TABLE is specifically aimed at splitting text into rows.
- Choose
SPLITfor an array result or when you will index or flatten the array later. - Choose
SPLIT_PARTfor a single known-position piece. - Choose
SPLIT_TO_TABLEfor a direct table of pieces.
These functions do not all share the same indexing conventions or output types. Check the position rules before replacing one with another, especially in queries that use a variable or user-provided position.
Common Problems and Data-Cleaning Choices
A frequent mistake is treating the delimiter as a regular expression. It is not: SPLIT searches for the exact separator string. If the source uses several different delimiters, such as commas and semicolons, a single SPLIT call cannot interpret them as alternatives. For pattern-based separation, Snowflake provides REGEXP_SPLIT_TO_ARRAY; use it only when a regular expression is genuinely needed, since the pattern has different rules from a literal delimiter.
Another common issue is inconsistent spacing. Values such as 'alpha, beta' and 'alpha ,beta' have spaces in different places, and splitting on a comma preserves those spaces. Decide whether to trim each result or standardize the source before processing. Trimming can be appropriate for tags, but it may be wrong for text where leading or trailing spaces carry meaning.
Before building downstream logic, check for cases that affect the shape or meaning of the result:
- Does the input contain NULLs, empty strings, leading separators, or trailing separators?
- Can separators appear inside a value, where they might need escaping or a more structured source format?
- Are consecutive delimiters valid empty fields, or should they be treated as bad data?
- Does every record have the same number and order of components?
Splitting is a text operation, not a full CSV parser. For example, a comma inside a quoted field may still be treated as a delimiter; ordinary CSV quoting rules are not automatically interpreted by SPLIT. If the data follows a structured format with quoting or escaping, use an appropriate file-format or parsing approach instead of assuming a simple split will preserve field boundaries.
Frequently Asked Questions
Does Snowflake SPLIT use a regular expression?
No. Its separator is a literal string. For a delimiter pattern, use a regular-expression function such as REGEXP_SPLIT_TO_ARRAY when that is suitable.
Does SPLIT return rows or an array?
It returns an array in one result value for each input row. Use FLATTEN or SPLIT_TO_TABLE if you need the pieces as separate rows.
Are SPLIT array indexes zero-based?
Yes. The first array element is at index 0. By contrast, SPLIT_PART numbers its requested part starting at 1.
Does SPLIT remove spaces around values?
No. It preserves the text on either side of the separator. Apply trimming separately if removing surrounding whitespace is appropriate for the data.
What happens when the delimiter appears twice in a row?
The result includes an empty string between the two delimiter occurrences. Leading or trailing separators can likewise create an empty element at the start or end.
Can SPLIT parse CSV text correctly?
Not by itself. It separates on literal delimiter occurrences and does not apply CSV quoting and escaping rules, so a quoted comma can still split a field.
Related
- Google Sheets SPLIT Function: How to Separate Text
- Oracle Split String Example: SQL Methods That Work
- Balance of Payments: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter No Duplicates? Show Unique ValuesExcel’s regular AutoFilter does not have a one-click option to show only unique values. To hide duplicate records…
- Excel Split Function: How to Split Text into ColumnsIn Excel for Microsoft 365, the function for splitting a text string is TEXTSPLIT. It separates text at…
- Count Unique Values in Excel Without DuplicatesTo count distinct, nonblank values in Excel, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) when your list is in A2:A100. The formula counts…
- Is INDIRECT Volatile in Excel? Performance ExplainedYes. INDIRECT is a volatile Excel function, so it recalculates whenever Excel recalculates, even when the cells named…
- AVERAGEIF Excel Range: Syntax, Examples, and Common FixesIn Excel, the range in AVERAGEIF is the group of cells Excel checks against your condition; the optional…
- Split Cells in Excel: Text to Columns and FormulasTo split the contents of a cell in Excel, select the cells, then choose Data > Text to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.