Snowflake’s SPLIT_PART function returns one piece of a string separated by a specified delimiter. Its syntax is SPLIT_PART(string, delimiter, partNumber): the part number is normally counted from 1, and a negative number counts from the end.
What SPLIT_PART Does
SPLIT_PART is a SQL string function for extracting one delimited segment without first returning every segment as an array. For example, it can retrieve a region from a path, a code from a colon-separated identifier, or a name from a consistently delimited value. It returns a string value, making the selected piece straightforward to use in a query, filter, or expression.
The function divides the input conceptually wherever the delimiter occurs, then returns the requested piece. It does not remove surrounding spaces, validate the meaning of the piece, or interpret a format such as CSV. If the requested piece is missing, Snowflake returns an empty string rather than an error. Those behaviors matter when building reports or joining extracted values to other data.
Syntax and Arguments
The three arguments are the input string, the delimiter that marks boundaries, and an integer specifying which piece to return. A basic query looks like this:
SELECT SPLIT_PART('north|west|zone-4', '|', 2) AS part;
The result is west. The delimiter here is a single vertical bar, but it can also contain multiple characters. Snowflake treats it as the literal separator, not as a regular expression. The part number should be an integer value; if it comes from a column or expression, make sure the resulting value is appropriate for the position you intend to select.
Each argument can be an expression rather than a fixed value. For example, the string might be a table column and the part number might be a constant. A practical query could be:
SELECT SPLIT_PART(event_path, '/', 2) AS second_segment FROM events;
That query extracts the second slash-separated segment from each row’s event_path. It does not change the stored value; it only calculates the result for the query.
How Part Numbers Work
Positive part numbers start at 1, not 0. In a string divided into pieces A, B, and C, position 1 returns A, position 2 returns B, and position 3 returns C. A position of 0 is treated as position 1, a detail that can surprise people familiar with programming languages where arrays commonly start at index zero.
Negative part numbers count from the end. In the same three-piece string, -1 returns C, -2 returns B, and -3 returns A. This is useful when the desired segment is defined by its relationship to the end, such as a file extension or the final component of a path, even when the number of preceding components varies.
For example, assuming the delimiter is a period, SPLIT_PART('archive.backup.gz', '.', -1) returns gz, while SPLIT_PART('archive.backup.gz', '.', -2) returns backup. A negative position refers to the pieces created by splitting on every occurrence of the specified delimiter; it does not recognize file-name conventions or special cases such as a hidden file beginning with a period.
Examples You Can Adapt
These examples show how the function behaves with different delimiters and positions:
SELECT SPLIT_PART('alpha,beta,gamma', ',', 1);returnsalpha.SELECT SPLIT_PART('alpha,beta,gamma', ',', 3);returnsgamma.SELECT SPLIT_PART('alpha,beta,gamma', ',', -1);also returnsgamma, counting from the right.SELECT SPLIT_PART('acct::482::open', '::', 2);returns482, using a two-character delimiter.SELECT SPLIT_PART('one/two/three', '/', 4);returns an empty string because there is no fourth piece.
In a table query, the string and delimiter are often stable while the extracted value is used in later logic. For example, a query can select SPLIT_PART(product_code, '-', 1) as a product family. If you filter on the extracted expression, check whether the desired result is an exact match and whether input values follow the same delimiter convention. A string with an unexpected format can still produce a result, but that result may not represent the intended business value.
Empty Pieces, Missing Parts, and NULLs
Repeated delimiters create empty pieces between them. For example, splitting 'red||blue' on '|' produces red, an empty second piece, and blue. Therefore, SPLIT_PART('red||blue', '|', 2) returns an empty string. An empty result does not always mean that the requested position was beyond the end; it can also mean the position exists but contains no characters.
A delimiter at the beginning or end can likewise create an empty piece at the corresponding edge. If the delimiter does not occur at all, the original non-NULL string is the first piece, while a request for a later piece returns an empty string. These cases make it useful to distinguish an empty extracted value from a missing or malformed input when the distinction matters to your application.
If any argument is NULL, SPLIT_PART returns NULL. That is different from an empty string: NULL represents an unknown or absent value, while an empty string is a string containing no characters. If downstream logic needs to treat either condition as a fallback, handle that deliberately with a suitable conditional expression rather than assuming the function will substitute a value.
An empty delimiter is a special case: Snowflake returns the input string as a single part. It does not split the string into individual characters. If character-by-character processing is the goal, use an approach designed for that task instead of expecting an empty delimiter to produce characters.
Choosing SPLIT_PART or Another Function
Choose SPLIT_PART when you need one known position from a string with a consistent, literal separator. It keeps a query compact and avoids constructing or handling an array when only one piece is required. Negative positions are especially convenient for extracting a segment counted from the end.
Use SPLIT when you need the collection of all pieces as an array, perhaps to inspect or process multiple elements. Snowflake’s SPLIT returns an array, whereas SPLIT_PART returns just one string segment. For a pattern-based match rather than a literal delimiter, consider a regular-expression function such as REGEXP_SUBSTR. Regex gives more flexible matching, but it also requires a pattern and can be harder to read or maintain.
Neither function is a full parser for structured formats. In particular, splitting a comma-separated value with SPLIT_PART is unsafe if commas may appear inside quoted fields, as they can in valid CSV. Use a format-aware loading or parsing method for CSV and other structured data. Similarly, a URL, file path, or identifier may have escaping rules or special cases that a simple delimiter split does not understand.
Common Mistakes and Practical Checks
The most frequent mistakes come from assuming the function does more than it does. Before relying on an extracted segment, check the actual delimiter, the position convention, and how irregular rows should be interpreted. A small sample query can reveal whether real data includes blank pieces, unexpected separators, or NULLs.
- Using zero as the first position: Snowflake treats 0 as 1, but writing 1 is clearer and avoids confusion.
- Expecting an error for an out-of-range position: the function returns an empty string, so validate the result if an absent segment should be flagged.
- Using a regular expression as the delimiter: the delimiter is literal. Choose a regex function for pattern matching.
- Forgetting consecutive separators: those create empty pieces, which shift later positions.
- Assuming whitespace is trimmed: a segment can retain spaces around its text. Apply an appropriate trimming function separately if needed.
It is also worth checking whether the data contains more than one delimiter style. A column that sometimes uses hyphens and sometimes underscores cannot be parsed consistently by one fixed delimiter without additional logic. Test representative edge cases, not just the cleanest example, and decide whether unexpected formats should produce an empty result, be filtered out, or be reported for cleanup.
Frequently Asked Questions
Is SPLIT_PART zero-based in Snowflake?
No. Positive positions start at 1. Snowflake treats a position of 0 as 1, so use 1 when you mean the first part.
Can SPLIT_PART count from the end?
Yes. Use a negative part number: -1 selects the final piece, -2 the piece before it, and so on.
What does SPLIT_PART return if the part does not exist?
It returns an empty string. Keep in mind that an existing piece can also be empty if delimiters are adjacent or occur at an edge.
Does the delimiter support regular expressions?
No. The delimiter is treated as a literal string, including when it contains multiple characters. Use a regular-expression function when the separator is a pattern.
What is the difference between SPLIT and SPLIT_PART?SPLIT returns an array containing the pieces, while SPLIT_PART returns one requested piece as a string. Choose based on whether you need one segment or the full set.
What happens when an argument is NULL?
If the string, delimiter, or part number is NULL, the result is NULL. That differs from an empty string, which is a non-NULL value with no characters.
Bottom Line
SPLIT_PART is the direct choice for extracting one segment from a string in Snowflake when the delimiter is known. Remember its 1-based positive positions, negative indexing from the end, literal delimiter behavior, empty-string results for unavailable pieces, and NULL propagation. Those details make it easier to write concise SQL without confusing a simple split with validation or full-format parsing.
Related
- Snowflake SQL SPLIT: Syntax, Examples, and Alternatives
- Snowflake LATERAL SPLIT_TO_TABLE: Syntax and Examples
- Train-Test Split: Purpose, Methods, and Common Mistakes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Link Quantities in Excel: A Step-by-Step GuideTo link a quantity from one worksheet to another in Excel, enter a cell reference such as =Inventory!B2…
- Google Sheets: Duplicate a Data Validation RuleTo duplicate a data validation rule in Google Sheets, copy a cell that already has the rule, select…
- Insert a Personal Signature in Word 365To insert a personal signature in Word 365, scan or photograph your handwritten signature, then choose Insert >…
- How to Add a Second Header in Word on Different PagesTo add a second line to a Word header, open the header area at the top of a…
- Google Sheets Can’t Open Apps Script? Fixes That WorkYes—Google Sheets can open Apps Script in a web browser: open the spreadsheet and choose Extensions > Apps…
- Excel Won’t Group Columns? Causes and FixesIf Excel will not group columns, select the column headers you want to fold, then choose Data >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.