To split a string at a delimiter in Snowflake, use SPLIT(string, delimiter); it returns an array of the pieces. For example, SPLIT('north|east|west', '|') returns an array containing north, east, and west. Use SPLIT_PART instead when you need one particular piece as a string, or SPLIT_TO_TABLE when you want one row per piece.
How Snowflake SPLIT works
SPLIT searches for the exact delimiter text and divides the input wherever that text occurs. The delimiter is a literal string, not a regular expression. It can be more than one character, so splitting on '||' treats the two pipe characters together as one separator. Snowflake returns the results as an array, which is useful when you want to keep the pieces together for later array operations.
For example, with a value such as 'sku-481-blue', splitting on a hyphen gives three elements. The result is not three separate SQL columns or rows: it is one array value in the result row. That distinction matters when choosing what to do next. Arrays are convenient for accessing or transforming pieces within a row; table functions are better when each piece should become its own record.
Basic use in a query looks like this:
SELECT SPLIT('sku-481-blue', '-') AS parts;
The returned array contains the text before, between, and after delimiter occurrences. If the delimiter does not appear, the array contains the original string as its only piece. A delimiter that occurs repeatedly can create empty pieces between occurrences; for instance, two adjacent commas represent an empty field between them. This can be important when the source uses delimiters to encode positions, such as a blank middle field in a delimited record.
Choosing between SPLIT, SPLIT_PART, and SPLIT_TO_TABLE
Snowflake offers related functions for different output shapes. Picking the one that matches the job avoids extra array handling or unnecessary row expansion.
SPLITreturns an array containing all pieces. Use it when you need the whole collection, or plan to index or flatten it.SPLIT_PARTreturns one specified piece as a string. Use it for a known field such as the second segment of a code.SPLIT_TO_TABLEis a table function that emits a row for each piece. Use it when you need to filter, join, group, or report on individual values.
For a value like 'north|east|west', SPLIT_PART('north|east|west', '|', 2) returns east. This is often simpler than splitting the entire string and then extracting an array element. If you need every segment as a separate row, use SPLIT_TO_TABLE rather than trying to treat the array returned by SPLIT as though it already had multiple rows.
Get one piece with SPLIT_PART
SPLIT_PART(string, delimiter, partNumber) selects a single piece using a position number. Positive positions count from the beginning: 1 is the first piece, 2 is the second, and so on. Negative positions count from the end: -1 selects the final piece, -2 the piece before it. A part number of 0 returns an empty string. These positions are useful for predictable formats, but they do not validate that the input actually follows the expected format.
For example, to get the file extension from a simple filename, you might use:
SELECT SPLIT_PART('report.csv', '.', -1) AS extension;
This returns csv. The example assumes that the final period separates the extension. A filename such as archive.tar.gz would return only gz, not tar.gz; that is a format decision, not a special rule of the function. Likewise, a missing delimiter may leave you with the original value instead of a meaningful field. If malformed or irregular input is possible, check it explicitly before relying on a position.
For a known, positive position, SPLIT_PART is often clearer than writing SPLIT(...)[index]. The latter uses array indexing, which starts at zero, unlike SPLIT_PART, which starts at one for its first part. Mixing those conventions is a common source of off-by-one errors.
Turn delimited values into rows
Use SPLIT_TO_TABLE when every delimited item should become a separate result row. It is a lateral table function, so it can use a column from the row being processed. The output includes SEQ, INDEX, and VALUE: INDEX identifies the item’s position, and VALUE contains the item text.
SELECT t.order_id, s.index, s.value
FROM orders AS t,
LATERAL SPLIT_TO_TABLE(t.tags, ',') AS s
ORDER BY t.order_id, s.index;
Here, assuming orders has an order_id and a comma-delimited tags column, an order with priority,fragile produces two rows. Keeping the original identifier in the select list lets you trace each split value back to its source row. The index is one-based, so the first item has index 1. Sort by the source identifier and index when you need a presentation order; SQL results are not guaranteed to appear in a particular order without ORDER BY.
Splitting into rows changes the number of results. A source row with five values can produce five output rows, which affects later joins, counts, and aggregations. Before using the expanded result in a larger query, decide whether the intended metric counts source records or individual split values. Those are different units, and counting after expansion can make a source record appear multiple times.
Access or flatten the array from SPLIT
If you want to keep using SPLIT, Snowflake array indexing can retrieve one element. Array indexes start at zero, so the first piece is at index 0. For example:
SELECT SPLIT('north|east|west', '|')[1]::STRING AS second_piece;
This selects east. The cast to STRING makes the intended text type explicit when using an array element in further expressions. Remember the indexing difference: array element 0 is the first piece, while SPLIT_PART position 1 is the first piece.
To expand an array into rows, use FLATTEN. For example, FLATTEN(INPUT => SPLIT(t.tags, ',')) can be used as a lateral table function, with its VALUE column holding each array item. This approach can fit naturally into a query that already creates or manipulates arrays. For a straightforward delimited column, SPLIT_TO_TABLE is often more direct and exposes the item position without first writing a separate array expression.
Literal delimiters, regular expressions, and data cleanup
Because SPLIT treats its separator literally, punctuation that has special meaning in regular expressions does not need regex escaping. Splitting at a period uses SPLIT(value, '.'); it does not mean “any character.” Conversely, you cannot pass a pattern such as “comma or semicolon” to SPLIT and expect it to interpret that as a regex. For pattern-based separation, Snowflake provides REGEXP_SPLIT_TO_ARRAY, which returns an array based on a regular-expression pattern.
For example, if a field uses either commas or semicolons as separators, a regex split can express that choice:
SELECT REGEXP_SPLIT_TO_ARRAY('red,green;blue', '[,;]') AS colors;
Choose regex only when the input format requires pattern matching. A literal split is easier to read and avoids regex-specific escaping and matching behavior. Also consider normalizing inconsistent source data: leading spaces after commas, inconsistent capitalization, or accidental delimiters inside a value may need their own cleanup rules. Splitting does not automatically trim whitespace or determine whether a piece is valid. You can apply trimming or validation to the resulting pieces as a separate step.
Common problems and edge cases
Many unexpected results come from assuming the delimiter or the data has a different shape than it actually does. Check the raw values and the exact separator before building downstream logic.
- Wrong delimiter: A pipe, comma, tab, and the two-character sequence
'||'are different separators. Make sure the expression matches the source format exactly. - Delimiter absent: If the separator is not present, there is no useful division to make; the original text remains a single piece. A position-based extraction may therefore return the whole input for a first-part request.
- Empty fields: Consecutive delimiters can represent a blank field. Do not discard empty pieces if their positions carry meaning, such as an optional middle component.
- Whitespace: A value like
'red, green'includes a leading space beforegreen. Splitting at the comma does not trim that space. - Nulls and empty strings: A SQL
NULLis not the same thing as an empty string. Decide how each should be handled in your query, and verify behavior against the actual input cases. - Quoted delimiters: A simple split does not parse CSV quoting rules. If a comma inside quoted text is part of a value rather than a separator, use a proper CSV ingestion or parsing approach instead of splitting the raw line.
Delimited strings are convenient but fragile when fields can contain the delimiter, when quoting and escaping rules exist, or when the number of fields varies unexpectedly. If you control the data model, storing repeated values as structured data or as separate rows can be safer than repeatedly parsing a manually assembled string.
Performance and practical design choices
For modest strings and ordinary queries, choose the function primarily by the result you need. SPLIT_PART avoids returning an array if you require only one position; SPLIT keeps all pieces available in one array; SPLIT_TO_TABLE expands items for relational work. These choices can also affect the shape and size of intermediate results. Expanding a large table with many pieces per value can substantially increase the number of rows a query processes.
If the same string is split repeatedly in several expressions, consider computing the split once in a subquery or common table expression, where that makes the query easier to understand. For recurring analytics, it may be more useful to parse the value during ingestion and store the fields in separate columns or a normalized child table. That can reduce repeated parsing and make filtering and joins clearer. Whether it improves performance depends on the workload, data volume, and query plan, so measure it with representative data rather than assuming one approach is always faster.
Finally, do not confuse splitting with a general-purpose file parser. A delimiter function handles text separation, not escaping, quoted fields, character encoding, or schema validation. When the source is a structured file format, use Snowflake’s appropriate loading and file-format features so that those rules are handled at ingestion.
Frequently Asked Questions
Does Snowflake have a SPLIT function?
Yes. SPLIT(string, delimiter) returns an array of pieces separated wherever the literal delimiter appears.
How do I get the second value after splitting?
Use SPLIT_PART(value, delimiter, 2) for a string result. Or use array indexing on SPLIT(value, delimiter); the second array item has index 1 because array indexes start at zero.
Can I split a Snowflake string into separate rows?
Yes. Use SPLIT_TO_TABLE(value, delimiter) as a lateral table function. Its VALUE column contains each item and INDEX indicates its one-based position.
Does SPLIT use regular expressions?
No. Its delimiter is literal text. For splitting by a pattern, use REGEXP_SPLIT_TO_ARRAY and provide a regular-expression pattern.
Can SPLIT parse CSV text with quoted commas?
Not by itself. A simple delimiter split will also divide at commas inside quoted values. Use CSV-aware file-format and data-loading features, or another parser that understands the quoting rules.
Related
- Split Sheet Meaning: What It Is and How It Works
- Snowflake SELECT SPLIT Example: Parse Delimited Strings
- Split Data in R for Training and Testing: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Databricks Built-In Functions: Types and ExamplesDatabricks built-in functions are ready-made operations for querying and transforming data with Databricks SQL and Apache Spark SQL.…
- Excel Number of Days Before Today: Formula and ExamplesTo find how many days have passed since a date in Excel, subtract the date from TODAY(). If…
- XLOOKUP Match Mode Examples: Exact and ApproximateXLOOKUP’s match_mode argument controls what counts as a match: use 0 for an exact match, -1 for an…
- Filter an Excel Table by Column: Steps, Tips & FixesTo filter an Excel table by column in Microsoft 365 for Windows, select the table, open the drop-down…
- Excel Filter Contains Text: Steps, Wildcards, and FixesYes. In Excel for Microsoft 365 on Windows, open the filter arrow on the text column, choose Text…
- Excel Formula If Cell Contains Value: Practical ExamplesTo return one result when a cell is not empty and another when it is empty, use =IF(A2<>"","Yes","No").…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.