Yes. In Snowflake, use SPLIT_TO_TABLE to turn a delimited string into one row per value; for example, a comma-separated field can become a set of rows with a lateral table-function call. You can also use SPLIT to make an array and FLATTEN to expand it when you need more control over the array.
The simplest way: SPLIT_TO_TABLE
SPLIT_TO_TABLE is the most direct choice when the input is a string and the goal is to produce rows. It accepts the string and its delimiter, then returns a table with a row for each piece. In a query over a source table, call it laterally so it can use a different string from each source row.
SELECT t.record_id,
s.index AS token_position,
s.value AS token
FROM source_table AS t,
LATERAL SPLIT_TO_TABLE(t.tag_list, ',') AS s
ORDER BY t.record_id, s.index;
This example assumes source_table has a unique record_id and a tag_list such as red,blue,green. The result has three rows for that record, with the values red, blue, and green. The lateral call is evaluated for each input row, so strings from different records are split independently.
The table function returns SEQ, INDEX, and VALUE. VALUE holds the substring, while INDEX identifies its position and starts at 1. SEQ identifies the input row associated with the generated output, but it is not a reliable ordering key. Keep the source table’s own key and sort by that key plus INDEX when sequence matters.
What lateral expansion does
A regular scalar function returns a value for each input row. A table function can return zero, one, or many rows instead. SPLIT_TO_TABLE is a table function, and LATERAL makes the current source row’s string available to it. The comma in the FROM clause combines each source record with the rows produced from that record.
This is not the same as splitting a string and placing the pieces in columns. A row expansion changes the shape of the result: a record with four delimited values contributes four output rows. Other selected columns from the source, such as a customer ID or date, can be included in the SELECT list and will repeat on each generated row.
For a one-off string rather than a source column, the table function can be called by itself:
SELECT index, value
FROM TABLE(SPLIT_TO_TABLE('red,blue,green', ','))
ORDER BY index;
This produces three values. Use the table form when exploring a literal or testing a delimiter, and the lateral form when splitting a column across a table.
Using SPLIT with FLATTEN instead
SPLIT returns an array, and FLATTEN turns array elements into rows. This combination is useful when you already have an array, want to work with array-oriented expressions, or need options provided by FLATTEN.
SELECT t.record_id,
f.index AS array_position,
f.value::STRING AS token
FROM source_table AS t,
LATERAL FLATTEN(INPUT => SPLIT(t.tag_list, ',')) AS f
ORDER BY t.record_id, f.index;
FLATTEN exposes columns including INDEX and VALUE. Unlike SPLIT_TO_TABLE, the array index from FLATTEN starts at 0. Cast VALUE to STRING when a string result is useful downstream; the flattened value is represented as a variant. Account for the different index bases if you switch between the two methods or combine their results.
For ordinary delimited text, SPLIT_TO_TABLE is usually easier to read. Choose SPLIT plus FLATTEN when the array itself is useful or when you need FLATTEN‘s behavior, such as its OUTER option for preserving inputs that otherwise produce no flattened rows.
Delimiters, empty values, and whitespace
The delimiter passed to SPLIT_TO_TABLE or SPLIT is literal text, not a regular expression. A comma splits on commas; a multi-character delimiter such as || is treated as that exact string. If the data uses different separators in different rows, normalize it first or use logic designed for that format rather than assuming one delimiter will cover every case.
Adjacent delimiters can represent an empty field. For example, splitting red,,blue includes an empty value between red and blue. Decide whether that empty position has meaning before filtering it out: in one dataset it may signal missing data, while in another it may be noise. A filter such as WHERE s.value <> '' removes empty strings, but it also changes the number and positions of values in the result.
Whitespace around tokens is not automatically the same as whitespace between tokens. If a value is red, blue, the second token contains a leading space. Apply TRIM when that space should be removed:
SELECT t.record_id,
s.index,
TRIM(s.value) AS token
FROM source_table AS t,
LATERAL SPLIT_TO_TABLE(t.tag_list, ',') AS s;
Do not trim blindly if spaces are part of the data. Also consider nulls and empty input separately. A null string is not a list of ordinary values, and a source row may fail to generate tokens. Test the actual null and empty-string cases in your query, especially if downstream counts or joins depend on whether an input has any values.
Keeping source rows with no tokens
A lateral expansion normally returns rows for the values it generates. If an input is null or otherwise produces no rows, that source record may not appear in the result. This matters when the requirement is “show every source record, even when it has no token,” rather than “show only records with tokens.”
With the SPLIT and FLATTEN approach, OUTER => TRUE asks FLATTEN to produce a row for a zero-row expansion, with nulls for the flattened fields:
SELECT t.record_id,
f.index,
f.value::STRING AS token
FROM source_table AS t,
LATERAL FLATTEN(
INPUT => SPLIT(t.tag_list, ','),
OUTER => TRUE
) AS f;
Check the output for the exact input cases in your data before relying on this pattern. In particular, distinguish a genuinely empty token from an input that generates no elements. If you need to preserve all source rows while using SPLIT_TO_TABLE, consider a left-lateral join pattern supported by your query and validate its behavior for null and empty strings. The important point is to make row preservation an explicit requirement rather than assuming expansion will retain every input.
Ordering and joining the results
SQL result rows have no guaranteed order unless the query includes ORDER BY. Even if a query appears to return tokens in input order, do not rely on that appearance. For a stable sequence, order by a unique source key and the function’s index, as in the earlier example. The index describes token position within one input; it is not necessarily unique across the full result.
When joining expanded tokens to a lookup table, normalize both sides consistently. If the token has surrounding spaces or inconsistent letter case, decide whether to apply TRIM or a case-normalization function before comparing. Be careful with filters: placing a condition on the expanded value in a WHERE clause can remove the null-extended rows you intended to preserve with an outer expansion.
It is often useful to retain both the source key and token position in a staging result. Those columns make it possible to trace an output token back to its original record and reproduce its order, even when token text repeats. Avoid treating the token alone as a unique identifier.
When splitting is the wrong tool
Delimited strings are convenient for display, but they are often a poor storage format for values that need to be searched, joined, validated, or updated individually. If you control the data model, storing one value per row in a child table is usually easier to query than repeatedly parsing a packed string. Ingesting structured data as an array can also be preferable to encoding an array as comma-separated text.
Do not use a simple comma split on CSV that allows quoted commas. For example, a field such as "New York, NY" contains a comma that is part of the value, not a separator. SPLIT_TO_TABLE treats the delimiter literally and does not implement CSV quoting rules. Parse the data with an appropriate CSV ingestion process or a format-aware parser before turning its fields into rows.
For data that is already JSON, use the JSON parsing and array-expansion tools rather than splitting its textual representation. Parsing JSON text into a semi-structured value and flattening its actual array respects the structure; splitting on commas in JSON text can break values that contain commas and does not account for quoting or nested objects.
Performance and practical checks
Splitting expands rows, so output size depends on both the number of source records and the average number of tokens per record. A table with many records and long lists can produce a much larger intermediate result than the source table. Select only the source columns needed, filter irrelevant source records before expansion where possible, and avoid expanding the same string repeatedly in separate parts of a query.
Before using the result for reporting or loading, check representative edge cases: a normal list, a single value with no delimiter, adjacent delimiters, leading or trailing spaces, null input, and any empty-string input your source can contain. Compare input counts with output counts only after accounting for rows that legitimately generate multiple tokens or no tokens. These checks help catch delimiter assumptions and explain why a row count changes after expansion.
Frequently Asked Questions
What is the easiest Snowflake function to split a string into rows?SPLIT_TO_TABLE is usually the simplest option. Use it laterally with a source column to return one output row for each delimited value.
Is the index returned by SPLIT_TO_TABLE zero-based?
No. SPLIT_TO_TABLE returns a one-based INDEX. The array index from FLATTEN is zero-based, so adjust comparisons or displayed positions when changing methods.
Does Snowflake split on a regular expression?
No. The separator supplied to SPLIT_TO_TABLE or SPLIT is literal text. Use a regex-aware approach when the delimiter is a pattern rather than a fixed string.
How do I remove spaces around each split value?
Apply TRIM to the returned value, such as TRIM(s.value). This removes surrounding whitespace; it does not change the delimiter or parse quoted CSV fields.
How can I keep rows whose string is null?
Use a row-preserving design. With FLATTEN, OUTER => TRUE can emit a null-valued flattened row for a zero-row expansion. Validate null and empty-string behavior for the exact query, and use a left-lateral strategy if retaining every source row is essential.
Related
- Snowflake SELECT SPLIT Example: Parse Delimited Strings
- Split Data in R for Training and Testing: A Practical Guide
- F-Strings in JavaScript: Use Template Literals Instead
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Freeze Rows Greyed Out? Causes and FixesIf Freeze rows is greyed out in Google Sheets, first make sure you can edit the spreadsheet and…
- Excel Cell Width in Inches: What the Number Really MeansExcel does not measure column width directly in inches: its width setting is based on the number of…
- How to Freeze More Than the Top Row in ExcelTo freeze more than the top row in Excel, select the cell directly below the rows you want…
- How to Freeze the Bottom Row in Excel While ScrollingExcel does not have a built-in command to freeze the bottom row of a worksheet. In Excel for…
- How to Make a Poster in PowerPoint: A Practical GuideTo make a poster in PowerPoint, create a blank presentation, set a custom slide size to the poster’s…
- Excel Find and Replace in Selection: Steps and TipsTo use Find and Replace only in selected cells in Excel for Microsoft 365 on Windows, select a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.