In Snowflake, use SPLIT inside a SELECT to break a string into an array: SELECT SPLIT('red,green,blue', ',') AS parts; returns the three pieces as an array. Use array indexing to select one piece, or flatten the array when you need one output row per piece.
A Basic Snowflake SPLIT Example
SPLIT takes a string and a separator, in that order. The separator is treated as a literal string, not as a regular expression. For example, this query splits on a pipe character:
SELECT SPLIT('east|central|west', '|') AS regions;
The result is an ARRAY containing east, central, and west. In Snowsight, Snowflake’s web interface, the returned array is displayed in a compact form; the key point is that the expression returns one array value in one row, not three separate rows or columns.
For a table column, pass the column name instead of a string literal. If a table named orders has a delivery_route column, the following returns each original route alongside its array:
SELECT order_id, delivery_route, SPLIT(delivery_route, ' - ') AS route_parts
FROM orders;
Here the delimiter includes a space on either side of the hyphen. That matters: SPLIT matches the delimiter exactly, so splitting on '-' instead would leave any surrounding spaces attached to the resulting elements.
How SPLIT Works and What It Returns
Conceptually, Snowflake scans the input string from left to right for occurrences of the supplied separator and puts the segments between matches into an array. A string with no matching separator becomes a one-element array containing the original string. A separator can contain more than one character, which is useful for delimiters such as ::, ||, or a tab represented in a SQL string.
The output is an ARRAY, rather than a scalar VARCHAR. That difference shapes what you can do next: keep the array as one value, retrieve an element, or expand the elements into rows. SPLIT is useful for simple, consistently delimited text, but it does not automatically trim spaces, validate the data, or interpret quoting rules.
For example, splitting 'apple, pear,plum' on a comma yields elements that include a leading space before pear. If you need normalized values, trim them after extracting or expanding the array. Also note that consecutive delimiters represent empty segments; splitting 'a,,c' does not silently remove the missing middle value. Keeping that empty element can be important when positions in the source have meaning.
Selecting One Element from the Array
Snowflake array positions are zero-based when you use bracket notation: the first item is at index 0, the second at 1, and the third at 2. Because an array element accessed this way is a semi-structured value, cast it to VARCHAR when you want an ordinary text result.
SELECT SPLIT('east|central|west', '|')[1]::STRING AS second_region;
This returns central. The cast makes the intended output type explicit, which is helpful when using the expression in comparisons, reports, or downstream transformations. You can apply the same pattern to a column:
SELECT
order_id,
SPLIT(delivery_route, ' - ')[0]::STRING AS origin,
SPLIT(delivery_route, ' - ')[1]::STRING AS destination
FROM orders;
This example assumes each route has at least two segments in the expected order. If the input format is inconsistent, check the array length or use a safer transformation before relying on a particular position. A direct index is convenient for a known fixed structure, but it is not a substitute for validating that structure.
Returning One Row per Split Value
To turn array elements into rows, use a lateral call to FLATTEN. This is useful when a record contains a list of tags, categories, or codes and you want to filter, group, or join on each individual value.
SELECT
t.customer_id,
f.index AS position,
TRIM(f.value::STRING) AS tag
FROM customer_tags AS t,
LATERAL FLATTEN(INPUT => SPLIT(t.tags, ',')) AS f;
For a row where tags is 'priority, renewal, online', the query produces three rows. f.value is the array element, and f.index identifies its position; array indexes from FLATTEN start at zero. The cast converts the VARIANT value into text, while TRIM removes the spaces around each tag. The original customer identifier is repeated for each resulting row.
To keep the position in the original list, select f.index. To keep only meaningful values, add a condition such as WHERE TRIM(f.value::STRING) <> ''. Be deliberate about filtering empty strings: an empty segment might indicate malformed input, but it could also be a meaningful placeholder in a positional record.
SPLIT Compared with SPLIT_PART
Choose SPLIT when you want an array or need to work with all the segments. Choose SPLIT_PART when you need one segment directly and know its position. For example:
SELECT SPLIT_PART('2026-11-03', '-', 2) AS month;
This returns 11. Unlike array bracket indexing, the part number supplied to SPLIT_PART is one-based: 1 means the first segment and 2 means the second. That difference is a frequent source of off-by-one mistakes when switching between the two functions.
SPLIT_PART is often simpler for a fixed-position field because it avoids creating an array just to retrieve one value. SPLIT is more flexible when you need several positions, want an array result, or plan to expand all elements. Neither function understands a complex data format merely because its fields contain delimiters; both work on strings and separators.
NULLs, Empty Segments, and Whitespace
Pay attention to how the source values are formatted before building transformations around them. If the input to SPLIT is NULL, the result is NULL, not an array of empty strings. A missing separator does not mean the input is automatically rejected; it remains a single segment. Consecutive delimiters can produce empty elements, so downstream logic should decide whether to preserve, filter, or flag them.
Whitespace is preserved. For instance, SPLIT('blue, green', ',') has a second element beginning with a space. If the value will be compared to a clean label such as green, trim the extracted value or trim each flattened value. Trimming the entire input before splitting only removes whitespace at the outer edges; it does not remove whitespace around every segment.
It is also worth distinguishing SQL NULL from an empty string. An empty string is a present string value, while NULL represents missing or unknown data. When data quality matters, inspect and handle both cases explicitly instead of assuming that a blank-looking result has only one interpretation.
When SPLIT Is Not the Right Tool
SPLIT is appropriate for simple delimiters, but it is not a full CSV parser. A comma-separated value that allows quoted fields can contain commas inside a field, escaped quotation marks, or line breaks. Splitting such a string on every comma breaks the field structure. If the source is genuine CSV, use a CSV-aware ingestion or parsing approach rather than treating every comma as a boundary.
Likewise, SPLIT does not accept a regular expression as its separator. If separators vary according to a pattern, look at Snowflake’s regular-expression functions, such as REGEXP_SUBSTR, or normalize the source first. For splitting a string directly into rows, Snowflake also provides the table function SPLIT_TO_TABLE. It can be a more direct option than creating an array with SPLIT and expanding it with FLATTEN; compare the returned columns and behavior against the shape your query needs.
For structured data that is already stored as JSON or another semi-structured type, query the structured value rather than converting it into a delimiter-based string and splitting it. Keeping data structured avoids ambiguity about separators and makes the intended fields easier to address.
Practical Query Checklist
Before putting a split expression into a production query, check the assumptions behind it. These small checks prevent many confusing results:
- Confirm the exact delimiter, including spaces, punctuation, or multiple characters.
- Decide whether the result should stay an array, provide one selected segment, or become multiple rows.
- Use zero-based indexes with array access and one-based part numbers with SPLIT_PART.
- Cast flattened or indexed values to the type expected by later expressions.
- Decide how to treat NULL inputs, empty segments, and whitespace.
- Verify that the source format does not allow quoting or escaping that makes simple splitting unsafe.
For an exploratory check, select the original text beside the split result and inspect a few representative rows, including unusual ones. A query that works for a clean example may still behave differently when a delimiter is missing, repeated, or surrounded by inconsistent spacing.
Frequently Asked Questions
Does Snowflake SPLIT return rows or an array?
SPLIT returns an ARRAY in a single result value. Use FLATTEN with the array if you want one output row per element.
Is Snowflake array indexing zero-based?
Yes. With bracket notation, index 0 is the first element and index 1 is the second. SPLIT_PART uses one-based part numbers instead.
Can the SPLIT separator be a regular expression?
No. SPLIT uses the supplied separator as a literal string. For pattern-based extraction, use an appropriate regular-expression function or another parsing approach.
How do I remove spaces after splitting?
Apply TRIM to each value. For row output, for example, use TRIM(f.value::STRING) after flattening the array.
What happens if a value has no delimiter?
The input remains a single element in the array. If the format requires a delimiter, add validation so malformed values can be identified rather than assumed to have multiple parts.
Related
- Snowflake SPLIT_PART: Syntax, Examples, and Common Issues
- JavaScript split() Explained: Strings, Limits, and Examples
- Microsoft Access Split Form: How It Works and When to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Remove Formatting in Word on a Mac: Step-by-Step GuideTo remove formatting in Word on a Mac, select the text or paragraphs, open the Home tab, and…
- Excel Text Filters: How to Find Matching Rows EasilyIn Excel for Microsoft 365 on Windows, select a cell in your data and choose Data > Filter,…
- Google Docs and Google Sheets: Differences and UsesGoogle Docs is Google’s word-processing app for writing and formatting documents; Google Sheets is its spreadsheet app for…
- How to Protect an Excel Sheet Without Locking EverythingTo protect an Excel sheet in Microsoft 365 for Windows, unlock any cells people should be able to…
- How to Double Space in Word on a Laptop: Quick StepsTo double-space text in Word on a laptop, select the text, then choose Home > Line and Paragraph…
- Monthly Calendar in Google Sheets: How to Create OneTo create a monthly calendar in Google Sheets, enter the month’s first date in a cell such as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.