Snowflake SPLIT_PART: Syntax, Examples & Tips

How-to basics · Updated November 22, 2026

In Snowflake SQL, SPLIT_PART(string, delimiter, partNumber) returns one piece of a string separated by a specified delimiter. The part number is normally one-based, so SPLIT_PART('north|central|south', '|', 2) returns central.

What SPLIT_PART does

SPLIT_PART is a string function for retrieving a specific segment without first turning the whole string into a visible list of values. It is useful when a column contains consistently delimited text, such as a file path, a code made of components, or an email address whose domain is needed for a report.

The function searches for the delimiter as a literal string and uses its occurrences to define the boundaries between parts. It does not treat the delimiter as a regular expression. For example, a period in the delimiter argument means a period, not the regular-expression wildcard for any character.

The function returns a VARCHAR value. It extracts text; it does not automatically convert a numeric-looking part into a number, validate an email address, or establish that a string follows a particular format. If you need a numeric result, convert the returned value separately and choose an appropriate conversion method for your data.

Syntax and part numbering

The general syntax is:

SPLIT_PART(string, delimiter, partNumber)

  • string is the text to split.
  • delimiter is the literal text that marks the boundary between parts. It can contain more than one character.
  • partNumber selects which part to return. Positive values count from the beginning, and negative values count from the end.

Positive numbering starts at 1, not 0. In Snowflake, a part number of 0 is treated as 1, so SPLIT_PART('a-b-c', '-', 0) returns a. Negative numbering can be convenient when the number of components varies: -1 means the last part, -2 the next-to-last part, and so on.

If the requested positive or negative position is beyond the available parts, the function returns an empty string. That is different from returning SQL NULL, which matters when downstream filters or calculations distinguish missing values from empty text.

Basic SQL examples

Here are common patterns. Each expression is shown in a query so that its returned value is clear:

  • SELECT SPLIT_PART('red,green,blue', ',', 2); returns green.
  • SELECT SPLIT_PART('team/region/report.csv', '/', -1); returns report.csv.
  • SELECT SPLIT_PART('ORD-2026-00481', '-', 2); returns 2026.

A column can be passed instead of a literal. For example, to select the domain portion after the at sign from a column named email_address, use SELECT SPLIT_PART(email_address, '@', 2) AS email_domain FROM customers;. This extracts text after the first matching at sign. It is not an email-validation rule: malformed values can produce unexpected-looking results, so validate or clean data separately when correctness depends on the address being well formed.

For a delimited code such as store|aisle|shelf, selecting position 2 returns the aisle component. If the delimiter does not occur in a string, the entire string is the first part; asking for a later part returns an empty string. That behavior can be useful, but it can also conceal inconsistent input if an expected delimiter is missing.

Multiple-character delimiters and empty parts

The delimiter does not have to be a single character. For instance, SPLIT_PART('alpha::beta::gamma', '::', 2) returns beta. Snowflake looks for the exact delimiter text, so using ':' instead would split at each colon and produce a different result.

Empty parts are possible when delimiters touch or appear at the edges. In 'a||c', splitting on | produces three positions: a, an empty middle part, and c. Therefore SPLIT_PART('a||c', '|', 2) returns an empty string. A leading or trailing delimiter can likewise create an empty position at the beginning or end. Do not assume an empty returned value proves the requested position was beyond the end; inspect the input format if that distinction matters.

If the delimiter argument is an empty string, Snowflake returns the input string. This is different from asking to split into individual characters. To split or analyze individual characters, use a method designed for that task rather than expecting an empty delimiter to act as a character boundary.

NULLs, missing fields, and safe handling

If any input argument is SQL NULL, SPLIT_PART returns NULL. For example, a null source string does not become an empty string, and a null delimiter or part-number expression also makes the result null. Keep this behavior in mind when a query combines extracted values with comparisons or aggregates.

There are three cases that are easy to confuse: a source value can be SQL NULL; a valid input can produce an empty string because a part is empty or out of range; or the input can contain the expected field. If reporting logic treats the first two cases alike, make that choice explicit. For example, NULLIF(SPLIT_PART(raw_code, '-', 3), '') changes an empty result to SQL NULL. Conversely, COALESCE(SPLIT_PART(raw_code, '-', 3), 'unknown') supplies a label only when the expression is null; it does not replace an empty string.

For data that may have unexpected separators, consider checking the extracted value and the original input together. A simple extraction can be perfectly valid SQL but still return a misleading field when the source format is inconsistent. Data-quality checks should reflect the rules of the particular field rather than assuming every row follows the same pattern.

SPLIT_PART compared with related functions

Choose the function based on the shape of the result you need. SPLIT_PART is direct when you want one known position. It avoids writing an array lookup when the query only needs one segment.

  • SPLIT(string, delimiter) returns an array of the separated values. Use it when you need to work with the collection as a whole or pass it to array-oriented operations.
  • SPLIT_TO_TABLE(string, delimiter) turns separated values into rows. This is useful when each component should become a separate record for filtering, joining, or aggregation.
  • REGEXP_SUBSTR is a regular-expression option when the pattern is more complex than a fixed literal delimiter. Regular expressions can be harder to read and maintain, so use them only when the input rules call for pattern matching.

For example, extracting the third item from a pipe-delimited field is a natural SPLIT_PART use. If you need to count or aggregate every item across many rows, returning one chosen segment is not enough; splitting to rows or using the array result may better match the task. Those alternatives have different output shapes, so they are not interchangeable merely because all can separate text.

Practical query and data-modeling considerations

SPLIT_PART is convenient in a SELECT list, a filter, or an expression used for grouping. For repeated reporting, however, consider whether a frequently needed attribute should be stored or exposed as its own structured column. A delimited string can be easy to ingest, but downstream users must repeatedly agree on delimiter rules, part positions, and handling for malformed records.

When using the function in a filter, apply the extraction to the intended source column and compare the resulting text carefully. For example, WHERE SPLIT_PART(product_code, '-', 1) = 'AB' selects rows whose first component is exactly AB. It does not automatically trim spaces or ignore case. If the source may contain whitespace or inconsistent capitalization, normalize it deliberately with the appropriate string functions and document that rule.

For large transformations, correctness and maintainability are usually more important than replacing a clear expression with a complicated workaround. Check representative inputs, including nulls, missing delimiters, repeated delimiters, and shorter-than-expected strings. If the extracted part feeds a join key, also verify that the same parsing and normalization rules are applied on both sides.

Common mistakes to avoid

  • Using zero as if it means the first position in every SQL system. Snowflake treats zero as position 1, but code moved between database platforms should be checked against each platform’s documentation.
  • Expecting a missing position to return NULL. An out-of-range position returns an empty string; a null argument returns null.
  • Passing a regular expression as the delimiter. The delimiter is literal text. Use a regex function when pattern-based matching is required.
  • Forgetting that adjacent delimiters create empty fields. The result may be empty even though the requested position exists.
  • Assuming extraction validates the source. A returned value does not certify that a path, code, or address meets a business rule.

Frequently Asked Questions

Is Snowflake SPLIT_PART zero-based or one-based?
It is one-based for positive positions: 1 is the first part and 2 is the second. Snowflake treats a part number of 0 as 1.

How do I get the last part of a string?
Pass -1 as the part number. For example, SPLIT_PART('a/b/c', '/', -1) returns c.

What does SPLIT_PART return when the delimiter is missing?
The full input is the first part, so position 1 returns the original string. A request for a later position returns an empty string.

Does SPLIT_PART return NULL when the part does not exist?
No. An out-of-range position returns an empty string. The result is null when any function argument is SQL NULL.

Can SPLIT_PART use a delimiter with multiple characters?
Yes. It matches the delimiter as literal text, so a delimiter such as '::' is treated as that two-character sequence, not as a regular expression.

Should I use SPLIT_PART or SPLIT?
Use SPLIT_PART to retrieve one known component. Use SPLIT when you need an array of all components, or a row-producing option such as SPLIT_TO_TABLE when each component should become a row.

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.