Yes—Snowflake has a built-in function called SPLIT_PART for returning one section of a string separated by a delimiter. Its syntax is SPLIT_PART(string, delimiter, part_number): part numbers start at 1, and negative numbers count backward from the end.
What SPLIT_PART Does
SPLIT_PART divides a string conceptually wherever the specified delimiter occurs, then returns the requested section as a string. It is useful when a value has a predictable, simple format, such as a code made of fields separated by a hyphen or a list of values separated by a comma.
For example, SPLIT_PART('2026-11-03', '-', 2) returns 11, the second field in the date-like text. The function does not convert that result to a number or date; it returns text. If a later operation requires a numeric or date value, convert it explicitly using an appropriate Snowflake conversion function.
The delimiter is a literal string, not a regular expression. It can contain more than one character, so SPLIT_PART('north::west::east', '::', 2) returns west. This can be more direct than extracting a field with a pattern when the separator is known.
Syntax and Part Number Rules
The function takes three arguments in this order: the source string, the delimiter to look for, and the position of the field to return. The part number controls which section is returned; it is not a character offset.
- Positive number: Counts from the left, beginning with 1. For
'a|b|c', part 1 isa, part 2 isb, and part 3 isc. - Zero: Snowflake treats a part number of 0 as 1, so it returns the first part.
- Negative number: Counts from the right. In
'a|b|c', -1 returnscand -2 returnsb.
A requested position that is beyond the available fields returns an empty string rather than raising an out-of-range error. For instance, asking for part 4 of 'a|b|c' returns an empty string. That behavior is important if the result is used downstream: an empty result may indicate that the source row did not contain the expected number of fields.
Basic SQL Examples
These examples use string literals so the result is easy to see. In a query against a table, replace the literal source with a column name.
SELECT SPLIT_PART('red,green,blue', ',', 2);returnsgreen.SELECT SPLIT_PART('account-4821-active', '-', 1);returnsaccount.SELECT SPLIT_PART('account-4821-active', '-', -1);returnsactive.SELECT SPLIT_PART('region::team::owner', '::', 2);returnsteam.
For a table, a query might look like SELECT SPLIT_PART(event_code, ':', 1) AS event_type FROM events;. This selects the first colon-separated component from every value in event_code. The alias event_type gives the output column a readable name; it does not change how the function splits the string.
To extract multiple fields, call the function once for each position. For example, selecting positions 1 and 2 from a pipe-delimited column can produce separate output columns. This is straightforward for a stable format with a small, known number of fields. If the number of fields varies or needs to be processed as a collection, an array-producing approach may be more suitable.
Delimiters, Empty Fields, and Missing Parts
Each exact occurrence of the delimiter marks a boundary. Leading, trailing, or repeated delimiters can therefore create empty fields. For example, SPLIT_PART('red,,blue', ',', 2) returns an empty string because there is nothing between the two commas. Similarly, the first field of '|red|blue' is empty, and the last field of 'red|blue|' is empty.
That is different from a missing part. In 'red|blue', part 3 is absent, and asking for it also returns an empty string. The function result alone therefore cannot always tell you whether the source contained an empty field or simply did not have that many fields. If that distinction matters, validate the input format separately or use additional logic to check the number and placement of delimiters.
An empty delimiter is a special case: Snowflake returns the original input string rather than splitting it into individual characters. A delimiter that does not occur in the source also does not create a split: part 1 is the original string, while a later requested part is empty. These cases can make a query appear to work while returning unexpected blanks, so check that the delimiter in the data matches the delimiter supplied in the SQL.
NULL Values and Data Quality
If the source string or delimiter is NULL, the result is NULL. This differs from an empty string, which is a real string value. For example, a query returning NULL may mean the source value is missing, while a query returning '' may mean the requested part was empty or unavailable. Keeping those cases separate is useful for reporting and validation.
Before building a transformation around SPLIT_PART, consider whether every source value follows the assumed layout. A field that contains an unexpected delimiter can shift the positions of later fields. A changed upstream format can also turn a previously populated position into an empty result without causing a SQL error.
- Inspect representative values, including short, long, blank, and malformed examples.
- Check whether delimiter characters can occur inside a field as ordinary content.
- Decide how to handle
NULL, empty strings, and missing fields; they are not interchangeable. - Where correctness matters, add validation or filters rather than assuming every row has the same structure.
SPLIT_PART Compared with SPLIT
Use SPLIT_PART when you need one known field from a delimited string. It expresses that goal directly and returns a string, making it convenient in a SELECT, filter, or grouping expression.
Snowflake’s SPLIT function instead returns an array of the pieces. That is useful when you need to work with the whole set of parts rather than request one position at a time. For example, an array can be useful when the number of values varies or when a later step should process the elements. Array access in Snowflake uses zero-based indexes, whereas SPLIT_PART uses positions beginning at 1; keep that difference in mind when switching between the functions.
Neither function automatically understands a full data format. A comma-separated string that allows quoted fields containing commas is not safely parsed just by splitting on commas. Likewise, splitting a file path or a structured code works only if the delimiter rules are consistent. Choose a parser that matches the actual format rather than treating every delimited value as a simple list.
When to Use Another Function
SPLIT_PART is not the best choice for every extraction. If the separator varies or the desired text is defined by a pattern, consider a regular-expression function such as REGEXP_SUBSTR. If the goal is to remove a known prefix or suffix, string functions such as SUBSTR or REPLACE may communicate the operation more clearly. For values with quoting, escaping, or nested structure, use a parsing method designed for that format rather than a simple delimiter split.
A practical rule is to match the function to the shape of the data:
- Fixed delimiter and known position: Use
SPLIT_PART. - Need all pieces as an array: Use
SPLIT. - Pattern-based extraction: Consider a regular-expression function.
- Structured JSON or other semi-structured data: Use the appropriate parsing and path-access features instead of splitting its text representation.
Common Mistakes to Avoid
A frequent error is using 0 when intending to request the first field and assuming the function uses zero-based positions. It does not: SPLIT_PART(text, delimiter, 1) is the explicit way to request the first part. Zero happens to return the first part as well, but relying on that behavior can confuse readers and is less clear.
Another common problem is expecting an out-of-range request to return NULL. It returns an empty string. If a downstream calculation or comparison treats empty text differently from NULL, that difference can affect results. Also remember that negative positions count from the end, which is useful for retrieving a final extension or status field but can conceal inconsistent field counts if the format changes.
Finally, do not assume the delimiter is a pattern. A period, a plus sign, or a backslash is treated as the literal delimiter text supplied to SPLIT_PART; regular-expression escaping rules do not apply to this function. For a multi-character separator, provide the complete separator as the second argument rather than only one of its characters.
Frequently Asked Questions
Is SPLIT_PART zero-based or one-based in Snowflake?
It is one-based for positive positions: 1 means the first part, 2 the second, and so on. A position of 0 is treated as 1. Negative positions count backward from the end.
What does SPLIT_PART return if the part does not exist?
It returns an empty string when the requested part is outside the available fields. This is not the same as NULL, so account for the difference when testing or transforming results.
Can the delimiter contain more than one character?
Yes. The delimiter is a string, so a value such as '::' can be used to split on a two-character separator. It is matched literally, not as a regular expression.
How do I get the last part of a string?
Use a negative part number of -1, as in SPLIT_PART(path_text, '/', -1). This returns the field after the final slash when the value uses slash-separated fields.
Does SPLIT_PART return an array?
No. It returns one string value. Use Snowflake’s SPLIT function when you need the pieces collected as an array rather than one selected field.
Related
- X++ Enum Values in SQL: How Stored Numbers Work in D365
- How to Add Speaker Notes to Your PowerPoint Slides
- What Is Balance in Accounting? Meaning and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Hide Columns Toggle Button: Options That WorkExcel for Microsoft 365 on Windows does not include a built-in ribbon button that alternates between hiding and…
- Project Plan Timeline Template Excel: Build One That WorksA useful project plan timeline template in Excel lists tasks, owners, start dates, durations, finish dates, and status,…
- Excel Max Row Count: How Many Rows Can a Sheet Hold?Microsoft Excel for Microsoft 365 supports a maximum of 1,048,576 rows per worksheet, numbered 1 through 1,048,576. That…
- Excel Shortcut Cheat Sheet for Windows and MacAn Excel shortcut cheat sheet is a quick reference to keyboard commands for editing cells, moving around worksheets,…
- Excel Audit Trail: What It Tracks and How to View ItYes, Excel can show an audit trail, but it is not a complete, permanent record of every action…
- Excel Data Validation Methods: Rules, Lists, and TipsExcel data validation methods control what users can enter in a cell: choose a built-in rule for numbers,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.