Snowflake LATERAL SPLIT_TO_TABLE: Syntax and Examples

How-to basics · Updated November 22, 2026

Yes. In Snowflake, use LATERAL SPLIT_TO_TABLE in the FROM clause to turn a delimited string in each source row into multiple rows. For example, FROM my_table AS t, LATERAL SPLIT_TO_TABLE(t.tags, ',') AS s produces one row per value in t.tags, alongside the original row’s data.

What “lateral split to table” means

The phrase usually refers to combining Snowflake’s SPLIT_TO_TABLE table function with the LATERAL keyword. The function divides a string at a specified delimiter and returns the pieces as rows. LATERAL lets the function refer to a column from a table expression that appears earlier in the same FROM clause.

That relationship matters when a table stores several values in one field. A row such as a product with the tags home, sale, blue can be expanded into three rows, each carrying the product’s identifier and one tag. This is useful for analysis or data preparation, though it does not change the stored source data.

In Snowflake, a table function is written directly in the FROM clause. You do not wrap SPLIT_TO_TABLE in TABLE(...). The lateral reference makes it possible to pass a different string from every source row into the function.

Syntax and returned columns

The general pattern is LATERAL SPLIT_TO_TABLE(string_expression, delimiter). The first argument is the string to split; the second is the delimiter to look for. The delimiter is literal text, not a regular expression. The function returns a table with three columns:

  • SEQ identifies the input record associated with a generated result. Do not treat it as a guaranteed ordering value or as a gap-free row number.
  • INDEX gives the position of the split value within that input string. Positions start at 1.
  • VALUE contains the resulting substring.

Give the function an alias and qualify its output columns with that alias, such as s.value. This is clearer than relying on unqualified names, especially when the source table has a column with a similar name. The output is not automatically sorted; use an explicit ORDER BY when result order matters.

A basic working example

Suppose a table named products has a unique product_id and a tags string. The following query expands each comma-delimited field and keeps the source identifier:

SELECT
  p.product_id,
  s.index AS tag_position,
  s.value AS tag
FROM products AS p,
LATERAL SPLIT_TO_TABLE(p.tags, ',') AS s
ORDER BY p.product_id, s.index;

If product 42 has home,sale,blue in tags, the query returns three rows for product 42. Their positions are 1, 2, and 3, and their values are home, sale, and blue. The ORDER BY uses the source key and position, rather than SEQ, to make the displayed order meaningful.

A small self-contained example makes the row expansion easier to see:

WITH source AS (
  SELECT 101 AS customer_id, 'red, blue,green' AS colors
  UNION ALL
  SELECT 102 AS customer_id, 'black' AS colors
)
SELECT
  t.customer_id,
  s.index AS position,
  s.value AS color
FROM source AS t,
LATERAL SPLIT_TO_TABLE(t.colors, ',') AS s
ORDER BY t.customer_id, s.index;

The first input row generates three output rows and the second generates one. Because the original customer identifier is selected, each generated value remains connected to its source. The space before blue is part of the split value; splitting does not trim whitespace for you.

How LATERAL relates the function to each row

Conceptually, Snowflake evaluates the table function using the relevant values from a source row, then combines the function’s output with that row. It repeats that process across the input. This is why the expression t.tags can appear inside SPLIT_TO_TABLE: t is introduced earlier in the FROM clause.

The comma form shown above is a common way to write this pattern. The lateral relationship is important when the function argument depends on a preceding table expression. If you are joining several source tables, make sure the table that provides the string is in scope before the lateral function. Use explicit aliases to make that dependency easy to read.

Do not assume that a lateral split behaves like a one-to-one join. It can increase the number of rows, and the increase varies according to how many pieces each input string produces. If you later join the expanded result to another table, check the join keys and expected row counts so that the expansion does not accidentally multiply records again.

Trimming, filtering, and preserving positions

Delimited data often includes spaces around values. To return cleaned values, apply TRIM to VALUE in the select list:

SELECT
  p.product_id,
  s.index AS original_position,
  TRIM(s.value) AS tag
FROM products AS p,
LATERAL SPLIT_TO_TABLE(p.tags, ',') AS s;

Keeping the original position can be helpful even after trimming. It tells you where a value appeared in the source string, which can help with troubleshooting or reconstructing the original sequence. If you filter values, remember that the output positions still describe positions in the input rather than renumbering the remaining results.

For example, to exclude blank or whitespace-only entries, use a filter such as WHERE TRIM(s.value) <> ''. This is different from merely trimming the output: trimming changes the displayed value, while the filter removes rows that are empty after trimming. Confirm how the source represents missing values and empty fields before applying such a filter, because removing them may discard information.

Delimiters and data-quality edge cases

Choose a delimiter that cannot be confused with ordinary content, or make sure the data format has a reliable escaping convention. SPLIT_TO_TABLE splits at the specified literal delimiter; it does not parse CSV quoting rules. For example, if a field contains red,blue as one quoted CSV value, splitting on a comma does not automatically recognize the quotes as protection for that comma.

Repeated delimiters and delimiters at the beginning or end can produce empty pieces. Those pieces may matter if positions have meaning, or they may be unwanted artifacts. Inspect representative input and decide whether to retain, trim, or filter empty values. Do not silently remove them if an empty field has a distinct meaning in the application.

Nulls and empty strings also deserve deliberate handling. Check how those inputs behave in your query and whether an input row with no usable tokens should appear in the result at all. A lateral expansion is designed to return rows for split results; it is not by itself a guarantee that every source row will remain represented in an output that has no corresponding result. If retaining every source record is required, plan and test that requirement as a separate part of the query.

SPLIT_TO_TABLE compared with SPLIT and FLATTEN

Use SPLIT_TO_TABLE when the desired result is directly a row for each substring. It gives you the position and value without first creating an array in a separate expression. This makes the intent clear for common delimited-string expansion.

SPLIT instead returns an array. That is useful when the next operation should work with an array as a value, rather than immediately expanding it into rows. You can also combine SPLIT with FLATTEN when you need capabilities of FLATTEN or are working with arrays and other semi-structured data. For a straightforward string-to-rows task, SPLIT_TO_TABLE is usually the more direct expression.

Neither approach turns an arbitrary delimited string into a robust structured format. If fields can contain the delimiter, or if quoting and escaping rules matter, use a proper parsing strategy for the actual format rather than assuming a simple split will interpret it correctly.

Performance and data-model considerations

Splitting strings at query time is convenient for exploration, reporting, and one-off transformations. Its main practical consequence is row multiplication: a table with one million source rows can produce substantially more than one million result rows if many strings contain multiple values. Estimate the expansion and avoid selecting unnecessary columns when working with large data.

If the same delimited field is repeatedly queried as separate values, consider whether the data should instead be stored in a normalized child table with one value per row, or in an appropriate structured type. A normalized design makes relationships and constraints easier to express and avoids repeatedly parsing text. The right choice depends on how the data is loaded, updated, and queried; a delimited string can still be reasonable for a simple attribute that is rarely analyzed individually.

For dependable results, retain a stable source identifier and use it with INDEX when ordering or tracing split values. Do not use SEQ as a substitute for a business key, persistent identifier, or guaranteed sort order. Also consider whether duplicate values are meaningful: SPLIT_TO_TABLE returns the pieces it finds, and does not deduplicate them.

Frequently Asked Questions

Do I need to write LATERAL before SPLIT_TO_TABLE?
Use LATERAL SPLIT_TO_TABLE(...) when the function references a column from a preceding table expression. That is the standard, readable form for splitting a different value from each source row.

Is INDEX zero-based or one-based in Snowflake?
INDEX is one-based: the first split value has index 1. Use it when you need the position of a piece in its original string.

Does SPLIT_TO_TABLE remove spaces around each value?
No. It splits at the delimiter but does not automatically trim the resulting values. Apply TRIM(s.value) if that is appropriate for your data.

Does the delimiter support regular expressions?
No. The delimiter argument is literal text, not a regular expression. For a delimiter that is more than one character, provide that literal string as the second argument.

Can I use SPLIT_TO_TABLE to parse CSV fields?
Not reliably when CSV quoting or escaping is significant. Splitting on commas does not interpret quoted commas as part of a field. Use a parser suited to the format or normalize the data before splitting.

Why did one source row become several rows?
That is the purpose of the table function: each piece becomes a separate output row, with source columns repeated alongside it. Check the number of delimiters and the input data, and use the source identifier with INDEX to inspect the resulting pieces.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.