Split a Column in Snowflake: SQL Methods and Examples

How-to basics · Updated November 22, 2026

To split a column in Snowflake, use SPLIT_PART(column_name, delimiter, position) when you need one specific piece, or SPLIT(column_name, delimiter) when you need all pieces as an array. For example, SPLIT_PART(full_name, ' ', 1) returns the first space-separated part of each value.

What splitting a column means in Snowflake

Splitting separates a text value wherever a delimiter occurs. A delimiter might be a comma, hyphen, pipe, space, or another exact string. If a column contains red,blue,green, splitting on a comma produces three parts. You can select one part, keep the parts together in an array, or turn them into separate rows.

The choice matters because these results have different shapes. SPLIT_PART returns a single text value for each input row. SPLIT returns an array value in each row. A table function such as FLATTEN can then expand the array into multiple rows. Splitting in a query does not, by itself, change the stored table or permanently add new columns. To retain a transformed result, you can use a view, a table-building query, or an appropriate data-loading transformation.

For example, imagine a table named orders with an order_code value of NW-2026-0042. You might extract the region, year, or sequence for analysis. Before doing so, check that the delimiter is meaningful and consistent: a hyphen inside a product name or customer-entered value can make a simple split ambiguous.

Choose the right Snowflake function

Use SPLIT_PART when the number and position of the desired piece are known. It is usually the clearest option for tasks such as taking the domain-like portion after an at-sign, extracting a fixed field from a consistently structured code, or selecting the first word. Use SPLIT if you need the complete set of pieces, want to inspect the result, or plan to expand the pieces into rows.

  • One selected piece: Use SPLIT_PART, which avoids returning an array when only one text value is needed.
  • All pieces in one result: Use SPLIT to return an array of strings.
  • One row per piece: Use SPLIT with LATERAL FLATTEN, or consider the SPLIT_TO_TABLE table function.
  • Variable separator patterns: Use REGEXP_SPLIT_TO_ARRAY when the separator is a regular expression rather than one fixed literal.

These functions do not infer a data format. If a value has optional fields, quoted delimiters, or inconsistent ordering, decide how those cases should be handled before choosing a method.

Extract a fixed piece with SPLIT_PART

SPLIT_PART takes the source text, the delimiter, and a part number. Positive part numbers count from the beginning, starting at 1. Negative part numbers count from the end. For example, if order_code contains NW-2026-0042, this query returns 2026 in the second output column:

SELECT
  order_code,
  SPLIT_PART(order_code, '-', 2) AS order_year
FROM orders;

The first piece would be SPLIT_PART(order_code, '-', 1), and the final piece can be requested with SPLIT_PART(order_code, '-', -1). This is useful when the part you need has a stable position, even if the total number of pieces is not important to the query.

If the requested position does not exist, SPLIT_PART returns an empty string rather than creating another row. That distinction can affect filters and reports: an empty string is not the same as SQL NULL. A null source value, on the other hand, results in a null output. If empty strings should count as missing, handle that explicitly, for example with NULLIF or a conditional expression.

Do not assume that splitting a person’s full name on a space reliably identifies first and last names. Names can contain multiple words, extra spaces, or different ordering conventions. The function will follow the delimiter and position you specify; it cannot determine the meaning of the pieces.

Return every piece with SPLIT

SPLIT returns an array, which is useful when a row can contain a variable number of values. For example, this query returns the tags as an array rather than choosing one tag:

SELECT
  product_id,
  SPLIT(tags, ',') AS tag_array
FROM products;

For a value of sale,new,blue, the array contains the three text elements in order. Snowflake array positions start at zero, unlike the one-based part numbers in SPLIT_PART. You can access an element by its array index and cast the value to text when needed:

SELECT
  SPLIT(tags, ',')[0]::STRING AS first_tag
FROM products;

This returns the first tag, if present. Use SPLIT_PART(tags, ',', 1) instead if your goal is simply to return the first piece as text. Keeping the array is more useful when later logic needs to work across all elements, such as expanding them, checking their count, or passing them to another array-aware operation.

Whitespace is not automatically removed from each piece. If the source is red, blue, the second result may include a leading space. You can trim individual values when selecting or expanding them, as shown in the handling section below. Also inspect whether the data uses a consistent delimiter: a comma followed by a space is not the same literal delimiter as a comma alone.

Expand a split value into separate rows

Sometimes the intended result is one row per item rather than an array held in one row. LATERAL FLATTEN expands the array produced by SPLIT. For example, given one product row whose tags value is sale,new,blue, this query produces three rows for that product:

SELECT
  p.product_id,
  TRIM(f.value::STRING) AS tag
FROM products AS p,
LATERAL FLATTEN(INPUT => SPLIT(p.tags, ',')) AS f;

FLATTEN exposes fields including VALUE and INDEX. The cast converts each array element to a string, and TRIM removes spaces at the beginning and end. If order is useful, select f.index as well; array indexes are zero-based. Rows from the original table are repeated for each array element, so a product with four tags contributes four output rows.

You can also use SPLIT_TO_TABLE when you want a table function to split a string into rows directly:

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

Choose between these approaches based on the next operation and the shape you need. FLATTEN is a general way to expand arrays and is handy if you already need the array. SPLIT_TO_TABLE expresses the row-splitting task directly. Test empty and null inputs against your intended output: by default, an empty expansion does not necessarily preserve the original row. FLATTEN offers an OUTER option when preserving a row for zero-row expansions is required, but review the resulting null values carefully.

Handle spaces, empty pieces, and nulls

Real data often contains small inconsistencies that a basic split does not fix. A value such as alpha, beta ,gamma has extra spaces around one item. Trim each piece if those spaces are not meaningful. For row expansion, the earlier TRIM(f.value::STRING) expression does this. For one selected piece, wrap the extraction in TRIM, such as TRIM(SPLIT_PART(tags, ',', 2)).

Repeated delimiters can produce empty pieces, and a delimiter at the beginning or end can leave an empty piece at an edge. Decide whether to retain those positions or treat them as missing. For example, an empty item in north,,south may indicate malformed input, or it may be an intentional placeholder. Removing it without checking the source’s meaning can shift positions and change the interpretation of later fields.

Null handling also deserves attention. SQL NULL represents an unknown or absent value; an empty string is still a string. In a filter, a condition such as extracted_value = '' does not identify nulls. If both should be considered missing, write logic that tests both cases, or normalize the data consistently in a view or transformation. Keep the original value available during validation so unexpected results can be traced back to the input.

When a regular expression or CSV parser is better

SPLIT uses a literal delimiter, not a regular expression. If values may use any one of several separators, Snowflake provides REGEXP_SPLIT_TO_ARRAY. For example, this expression splits a value wherever it contains a comma, semicolon, or vertical bar:

SELECT REGEXP_SPLIT_TO_ARRAY(mixed_list, '[,;|]') AS pieces
FROM source_data;

Use a regular expression only when the pattern is genuinely needed. It is less straightforward to read than a literal delimiter, and it still does not understand the business meaning of the fields. If separators can occur inside quoted values, a regex split may not be a safe substitute for a proper parser.

A common misconception is that splitting on a comma correctly parses every comma-separated file. It does not handle CSV quoting rules by itself. For example, a field such as "Seattle, WA" contains a comma that belongs inside a quoted field, not between fields. When loading a CSV file, configure and use Snowflake’s supported CSV file format and loading process so quoting and other file rules can be interpreted. For already stored, properly parsed fields, SPLIT is suitable when the delimiter is genuinely unambiguous.

Common mistakes and practical checks

Most split errors come from assumptions about the source rather than a complicated SQL issue. Before relying on a transformation in a report or pipeline, check representative values, including unusual and missing cases. A query that works for a clean example can still return misleading data if delimiters are inconsistent or appear within the content.

  • Mixing up indexing: SPLIT_PART starts at 1; array subscripts such as those used with SPLIT start at 0.
  • Expecting automatic cleanup: splitting does not trim spaces, change case, or validate the resulting pieces.
  • Expecting a schema change: selecting split expressions returns derived query columns; it does not alter the source table.
  • Using the wrong delimiter: a comma and a comma-plus-space are different strings. Confirm the exact data pattern before writing the expression.
  • Ignoring variable structure: a fixed part number may be empty for some rows. Check whether that is acceptable, and whether the total number of pieces should be validated.

For repeatable transformations, put the logic in a view or in the query that builds the destination table, then document the expected delimiter and edge-case behavior. If a value is used as a key or drives a business decision, validate the split output against the original data instead of assuming every row follows the same pattern.

Frequently Asked Questions

How do I split a column into two columns in Snowflake?
Use two SPLIT_PART expressions in the select list, with the same source and delimiter but different part numbers. For example, SPLIT_PART(code, '-', 1) and SPLIT_PART(code, '-', 2) return the first two hyphen-separated pieces. If the input may have more or fewer pieces, decide how those cases should be handled.

Does SPLIT_PART use zero-based or one-based positions?
SPLIT_PART uses one-based positive positions: 1 means the first part and 2 means the second. Negative positions count from the end. This differs from indexing an array returned by SPLIT, where the first element is at index 0.

How can I split one row into multiple rows?
Use LATERAL FLATTEN(INPUT => SPLIT(column_name, delimiter)), or use LATERAL SPLIT_TO_TABLE(column_name, delimiter). Both approaches produce rows for the pieces; include the source row’s identifier so you can tell which original record each piece came from.

Can Snowflake split on more than one delimiter?
For a regular-expression pattern, use REGEXP_SPLIT_TO_ARRAY and then expand the resulting array if separate rows are needed. A simple SPLIT call takes one literal delimiter, so it will not treat several different characters as interchangeable separators.

Why does my split result contain spaces or blank values?
The function preserves what is present around the separators; it does not automatically trim values or discard empty pieces. Apply TRIM when surrounding whitespace is unwanted, and decide explicitly whether blank pieces represent missing data or meaningful positions.

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.