Snowflake’s SPLIT function divides a string wherever it finds a specified delimiter and returns the pieces as an array. Use SPLIT_PART instead when you need one particular piece as a string, or flatten the array when you need each piece on its own row.
What the Snowflake SPLIT function does
SPLIT is a SQL string function for separating delimited text. Its two inputs are the source string and the delimiter: the delimiter is the exact text that marks a boundary between pieces. The result is an array, not a regular string and not a set of rows. For example, splitting 'red,green,blue' on a comma produces an array containing red, green, and blue.
This distinction matters when you use the result in a query. An array is useful for accessing elements, passing a collection to another function, or turning its contents into rows. It does not automatically create three output rows. If you want rows, use a table function such as FLATTEN or SPLIT_TO_TABLE.
Splitting is a parsing convenience, not a full validation step. It does not establish that a string follows a particular data format, remove unwanted spaces, or confirm that every resulting part is meaningful. Those checks need to be handled separately.
Syntax and return value
The basic syntax is SPLIT(string, separator). The first argument is the text to divide; the second is the literal separator to search for. The function returns an array of string values. Both arguments are required. If either argument is NULL, the result is NULL.
The separator is text, not a regular expression. For example, a period in the separator means a period character; it does not mean “any character,” as a period often does in a regular expression. The separator can also contain more than one character. If it is '::', Snowflake looks for that two-character sequence as a unit.
Here are representative calls:
SPLIT('one,two,three', ',')returns an array with three elements.SPLIT('one::two::three', '::')uses the full double-colon delimiter.SPLIT('one,two,three', '|')finds no pipe, so the source remains one element in the returned array.
Snowflake displays array results in its query output using bracket notation. Array elements are indexed from zero when you retrieve them by position, even though many SQL functions that select a “part” use one-based numbering.
Basic examples
Suppose an application stores a date-like value in a text column using hyphens. To see the components as an array, call SPLIT('2026-11-03', '-'). The output contains 2026, 11, and 03. This operation only separates the text; it does not convert the values into a Snowflake DATE. For date arithmetic, parse or cast valid input to a date instead of relying on an array of strings.
To split a path-like value, a multi-character delimiter works too: SPLIT('region::store::item', '::') produces three elements. A delimiter is matched as a whole, so choosing ':' would split at each colon and produce a different result. Make sure the separator you pass matches the source format.
You can retrieve an element from the result with GET. For example, GET(SPLIT('north|south|west', '|'), 1)::STRING returns south. Here the index is zero-based: index 0 is north, index 1 is south, and index 2 is west. The cast converts the returned value to a SQL string, which can be useful when using it in comparisons or displaying it as text.
When your goal is simply to select one part, SPLIT_PART is often clearer. It returns a string rather than an array, and its part number is one-based. For example, SPLIT_PART('north|south|west', '|', 2) returns south. Keeping the zero-based array index and the one-based part number straight prevents a common off-by-one mistake.
Delimiters, empty pieces, and whitespace
Splitting does not discard empty fields between delimiters. For example, SPLIT('a,,c', ',') preserves an empty element between a and c. A delimiter at the beginning or end of a string can also create an empty piece at that position. This behavior is important when empty fields represent missing values in a delimited record: silently ignoring them could shift later values into the wrong columns.
Spaces are not automatically removed. If the source is 'red, green, blue', the second and third elements begin with a space. You can trim individual elements after splitting, or trim the source where appropriate, but consider whether spaces are meaningful in your data before removing them. For instance, trimming a free-form field could change a value that is supposed to preserve leading or trailing spaces.
A delimiter that does not occur in the source leaves the source as one piece. An empty delimiter is usually not useful for parsing: Snowflake treats it as a request to return the input as a single element, rather than splitting the string into individual characters. If the source itself is empty, or if empty input has special meaning in your application, verify the resulting value and handle that case explicitly. Do not assume empty text and NULL are interchangeable; NULL means no value, while an empty string is a present value with no characters.
For dependable parsing, check how the producer represents missing fields, repeated delimiters, quoting, and embedded delimiters. A simple split is not a complete CSV parser: a comma inside a properly quoted CSV field, for example, should not necessarily act as a field boundary. Use an appropriate file format or parsing process for structured CSV data rather than treating every comma-separated string as uncomplicated text.
Turning array elements into rows
To return one row per piece, use FLATTEN with the array produced by SPLIT. For example, if a table named products has a text column named tags, the following query expands each non-null tag string:
SELECT p.tags,
f.index,
f.value::STRING AS tag
FROM products AS p,
LATERAL FLATTEN(INPUT => SPLIT(p.tags, ',')) AS f;
FLATTEN returns metadata as well as the array element. In this query, f.value is the element and f.index records its zero-based position. Casting the value to STRING makes the intended text type explicit. The lateral relationship lets the table function operate on each row’s own tags value.
If whitespace after commas is not wanted, trim the flattened value, for example with TRIM(f.value::STRING). If repeated delimiters produce empty elements, decide whether those should remain in the result or be filtered out. Filtering them changes the data, so do it only when empty entries are known to be irrelevant.
Another option is SPLIT_TO_TABLE, which is designed to split a string into table rows directly. For a single input, SELECT value FROM TABLE(SPLIT_TO_TABLE('a,b,c', ',')) returns the pieces as rows. Choose between this and FLATTEN based on the query: SPLIT_TO_TABLE is direct for splitting text into rows, while FLATTEN is convenient when you already have an array or need its index and other flattening metadata.
SPLIT compared with related functions
The right function depends on the shape of the result you need and how complicated the delimiter rule is:
SPLITreturns an array containing all pieces. Use it when you need a collection or intend to expand it later.SPLIT_PARTreturns one selected part as a string. Use it for a known position, such as the second segment of a simple code.SPLIT_TO_TABLEreturns split values as rows. Use it when a row-based result is the goal.REGEXP_SPLIT_TO_ARRAYsplits using a regular-expression pattern. Use it when the boundary is a pattern rather than one fixed literal delimiter.
For example, if every code has the form department-employee-number and you only need the department, SPLIT_PART(code, '-', 1) is more direct than producing an array and extracting its first element. If the rule is “split on any run of commas or semicolons,” a regular-expression splitting function is a better fit than repeatedly trying to use a literal separator.
These choices can also affect readability. A query that needs every token is usually easier to understand when it clearly shows the array expansion. A query that needs only one known field is easier to follow when it says which part it wants. Neither choice removes the need to check for malformed input or missing parts.
Nulls, data quality, and common problems
Before using SPLIT on a production column, inspect the actual source values. Values that look similar to a person may have different delimiters, extra spaces, missing pieces, or inconsistent capitalization. The function only applies the boundary you specify; it does not normalize those variations for you.
- Unexpected array output: Remember that
SPLITreturns an array. UseGETor array access for an element, or flatten the result to create rows. - Wrong element selected: Array indexes start at zero, while
SPLIT_PARTuses part numbers starting at one. - Unexpected spaces: The delimiter separates text but does not trim adjacent spaces. Apply
TRIMonly if that matches the data rules. - Missing values:
NULLinput producesNULL, while empty text and empty pieces are separate cases. Handle them intentionally. - Delimiter mismatch: A multi-character delimiter must match the source exactly. A regular-expression pattern cannot be passed to ordinary
SPLITto make it behave like a regex function.
For a field expected to have a fixed number of pieces, consider checking the parsed result before treating its positions as reliable. A record with fewer components than expected may produce an empty result when a missing part is requested from SPLIT_PART; that can be difficult to distinguish from a genuinely empty field unless you validate the original data as well.
Frequently Asked Questions
Does Snowflake SPLIT return an array or a table?SPLIT returns an array. It does not create additional query rows. Use FLATTEN or SPLIT_TO_TABLE when you want the pieces in separate rows.
Is Snowflake SPLIT_PART zero-based?
No. SPLIT_PART uses part numbers starting at 1. By contrast, array indexes used to retrieve elements from a SPLIT result start at 0.
Does SPLIT remove spaces around each value?
No. Spaces remain part of the resulting pieces unless you explicitly trim them. Check whether spaces are meaningful before applying TRIM.
Can the SPLIT delimiter be a regular expression?
No. SPLIT searches for the literal separator text. For a pattern-based boundary, use Snowflake’s regular-expression splitting function, REGEXP_SPLIT_TO_ARRAY.
What happens when the delimiter is missing?
The input is returned as a single array element because there is no boundary to split on. If a delimiter is present more than once, each occurrence separates pieces, including empty pieces where delimiters are adjacent.
What is the difference between an empty string and NULL?
An empty string contains no characters but is still a value; NULL represents a missing or unknown value. SPLIT returns NULL when its source or separator is NULL, so handle empty input separately when it matters.
Related
- Line of Balance Spreadsheet: Build and Track Projects
- Balance of Payments Diagram: Structure and Examples
- Can You Play Split Fiction on Mac?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concatenate Dates in Excel Without Losing Their FormatIf A2 contains Invoice date: and B2 contains March 4, 2026, join them with =A2&" "&TEXT(B2,"mmmm d, yyyy").…
- Excel Not Null Formula: Check for Nonblank Cell ValuesExcel has no built-in ISNOTNULL or NOTNULL function. To test whether A2 is not empty, use =A2<>""; it…
- IFERROR Function in Excel: Syntax, Examples, and TipsIn Excel, use IFERROR to return a chosen result when a formula produces an error: the syntax is…
- Excel Formula for File Path: Get the Workbook LocationIn Excel for Microsoft 365 on Windows, use =CELL("filename",A1) to return the saved workbook’s path, file name, and…
- Average VLOOKUP in Excel: How to Average Matching RowsTo average values for every row that matches a lookup value, use AVERAGEIF rather than VLOOKUP. For example,…
- Excel Not Blank Criteria: COUNTIF, SUMIF, and Practical ExamplesIn Excel, use the not-blank criterion "<>" in functions such as COUNTIF, COUNTIFS, SUMIF, and SUMIFS. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.