Yes. Snowflake’s SPLIT_TO_TABLE table function splits a delimited string into separate rows, returning the pieces in a column named VALUE along with a position in INDEX. Use it in a FROM clause; when the string comes from a table row, use it laterally so the function runs for each input row.
What SPLIT_TO_TABLE Does
SPLIT_TO_TABLE is useful when a field contains multiple values packed into one string and a query needs to treat each value as its own row. For example, a record with red,blue,green can produce three rows, one for each color. This is a row-generating table function: it changes the shape of query results rather than editing the original field or automatically creating a permanent table.
The function accepts two arguments: the input string and the separator to look for. The separator is a literal string, not a regular expression. A comma separates on commas; a pipe separates on the pipe character. If the delimiter is more than one character, Snowflake looks for that exact sequence.
Its result has three columns:
SEQidentifies the input record associated with the generated rows. It is useful as a query-level grouping aid, but it is not a substitute for a business key.INDEXgives the one-based position of a piece within the split input: the first piece is 1, the second is 2, and so on.VALUEcontains the piece of text. It remains text; the function does not infer a number, date, or other data type.
Basic Syntax
For a fixed string, put the function in the FROM clause inside TABLE:
SELECT seq, index, value
FROM TABLE(SPLIT_TO_TABLE('red,blue,green', ','))
ORDER BY index;
This returns three rows with values red, blue, and green, and indexes 1, 2, and 3. ORDER BY index makes the displayed order explicit. Without an ORDER BY, SQL does not promise that rows will be returned in a particular order, even if the function produces position numbers.
The table function can also be written with a lateral join when it refers to a column from an earlier table in the FROM clause. That correlated form is the usual pattern for splitting values stored in a table.
Split a Column into Rows
Suppose an orders table has an order_id column and an item_list string containing comma-separated item names. This query returns one row per item:
SELECT
o.order_id,
s.index AS item_position,
s.value AS item
FROM orders AS o,
LATERAL SPLIT_TO_TABLE(o.item_list, ',') AS s
ORDER BY o.order_id, s.index;
The lateral function can see o.item_list because it appears after orders in the FROM clause. For each order, Snowflake applies the split to that order’s value and returns the generated rows alongside the order identifier. If one order contains four items and another contains two, the result has six item rows, assuming both inputs split into those numbers of pieces.
Keep the source key in the result. The item name alone may not identify which original record it came from, especially when different orders contain the same item. A reliable identifier such as order_id, combined with the position when appropriate, makes the output easier to inspect, join, and aggregate.
Understanding SEQ, INDEX, and VALUE
INDEX is the position within a particular input string, not a globally unique row number. In two different orders, the first item can have index 1 in both. Use the source table’s key to distinguish those records. SEQ is generated to associate output with an input record, but Snowflake does not guarantee that sequence values are gap-free or ordered. Do not use it as a lasting identifier or assume that sorting by it recreates a meaningful business order.
For a stable, readable result, select the source key and sort by that key plus INDEX, as in the order example. If the source table can contain duplicate keys, include another column that uniquely identifies the original row. The function reports the textual pieces and their positions; it does not know whether a repeated item is a duplicate error or an intentional repeated value.
When a split piece should be treated as a number, date, or other type, convert it deliberately after splitting and account for invalid input. For instance, a numeric field can be passed through TRY_TO_NUMBER in the select list so malformed pieces do not cause the same kind of conversion failure as a strict cast. Choose the conversion that matches the data and how errors should be handled.
Delimiters, Empty Pieces, and NULL Values
Splitting is based on the exact separator supplied. A comma does not automatically recognize semicolons, tabs, or newlines. If the source data uses inconsistent separators, standardize it before splitting or use a preprocessing expression suited to the format. Do not assume the delimiter is a regular expression: a separator such as . means a literal period for this function, not “any character.”
Whitespace is also part of the text. A value such as tea, coffee can produce a second piece that starts with a space. If that space is not meaningful, use TRIM(s.value) in the projection. Trimming is a data-cleaning choice, though: it may be wrong if spaces are significant in the values.
Consecutive delimiters can create empty pieces, so a,,b should not automatically be treated as equivalent to a,b. Empty values may indicate missing data, or they may be valid positions in the source format. Decide whether to retain or remove them. For example, a filter such as WHERE TRIM(s.value) <> '' removes blank pieces after trimming, but it also discards any intentionally empty value.
Check how null and empty input values behave in the specific query and data shape you are handling rather than assuming they mean the same thing. If every source row must remain in the output even when there are no pieces to expand, plan for that requirement explicitly; an ordinary row-generating function can change which source rows appear. Test representative null, empty, and delimiter-only strings before using the result downstream.
How It Differs from SPLIT and FLATTEN
Snowflake’s SPLIT function also separates a string, but it returns an array. Use SPLIT when an array is the useful result, such as when you want to work with the collection as one value. Use SPLIT_TO_TABLE when each piece should become a row and you want the position and value exposed as table columns.
FLATTEN expands arrays, objects, or other supported semi-structured values into rows. A common alternative pattern is to call SPLIT to make an array and then flatten that array. That can be appropriate when the query already needs an array or needs additional array processing. For the straightforward task of splitting one delimited string into rows, SPLIT_TO_TABLE is usually more direct and provides INDEX and VALUE in its output.
Neither approach repairs poor source data by itself. If values can contain the delimiter as part of a quoted field, a simple delimiter split may break the value incorrectly. For example, splitting Smith, Jr.,Ana on commas cannot infer that the first comma belongs inside a name. Use a format-aware ingestion or parsing method for quoted, escaped, or nested data instead of treating it as a plain delimited string.
Using the Results in a New Table
The function itself does not save its output. A query that uses it returns a result set; to persist the expanded rows, use the query in a table-creation or insertion workflow. For example, a CTAS statement can store the split output:
CREATE TABLE order_items AS
SELECT
o.order_id,
s.index AS item_position,
TRIM(s.value) AS item
FROM orders AS o,
LATERAL SPLIT_TO_TABLE(o.item_list, ',') AS s;
This creates a table from the query result, subject to the permissions and object-name rules in the Snowflake account. Review the selected columns and row counts before using a materialized result in reports. If source data changes later, the created table does not automatically update just because the original query used a table function; refresh or rebuild it through the appropriate data pipeline.
Also consider whether splitting is a one-time cleanup or a recurring query step. Repeatedly parsing a packed string can make downstream logic harder to maintain. Where the data model and ingestion process allow it, storing one item per row in a related table is often clearer for joins, constraints, and analysis. Splitting remains useful for importing legacy fields, processing tags, or exploring data before changing the source model.
Frequently Asked Questions
Does SPLIT_TO_TABLE create a permanent table?
No. It returns rows as part of a query. To save them, use a statement such as CREATE TABLE ... AS SELECT or insert the query result into an existing table.
Is INDEX zero-based or one-based?
It is one-based: the first piece has index 1. It represents the position in an individual input string, so it can repeat for different source records.
Can I split a column for every row in a Snowflake table?
Yes. Put the source table before LATERAL SPLIT_TO_TABLE in the FROM clause and pass the column to the function. Include the source row’s key in the select list to keep the expanded values connected to their origin.
Can the separator be a regular expression?
No. SPLIT_TO_TABLE uses the separator as a literal string. If the input uses a pattern of different separators, normalize the text or select a parsing approach designed for that data.
How do I keep the pieces in their original order?
Select INDEX and explicitly sort by the source record key and then INDEX. Do not rely on the physical order of query results or on SEQ as an ordering guarantee.
Related
- Balance of Payments Notes: Accounts, Examples and Meaning
- Java split(): How It Handles Delimiters and Limits
- Java String split(): Syntax, Regex, and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Protect Sheet in Excel: Allow Editing in Certain CellsYes. In Excel for Microsoft 365, you can protect a worksheet while leaving particular cells editable: unlock those…
- Monthly Expenses Tracker in Google Sheets: Setup GuideYes—you can make a monthly expenses tracker in Google Sheets by recording each purchase on a Transactions tab…
- Resize Excel in Word: Fit a Spreadsheet to the PageTo resize Excel content in Word, select it and drag a corner handle inward or outward; for a…
- How to Archive a Lot of Emails in Outlook at OnceTo archive a large batch in Outlook, narrow the message list with a search or folder filter, select…
- Google Sheets Inventory System: Setup, Limits, and TipsYes. You can build a basic inventory system in Google Sheets by keeping a product list and a…
- Currency Conversion in Google Sheets: Formulas and TipsTo convert an amount in Google Sheets, multiply it by a rate returned by GOOGLEFINANCE. For example, if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.