Snowflake’s SPLIT_PART function returns one piece of a string separated by a delimiter. Its basic syntax is SPLIT_PART(string, delimiter, part_number): the part number starts at 1, and a negative number counts backward from the end.
What SPLIT_PART does
SPLIT_PART is useful when a column stores several values in one string and you need one value for a query, report, or transformation. For example, it can return the domain from an email address, a folder name from a path, or a field from a simple delimiter-separated code.
The function looks for the delimiter you supply, divides the string at each occurrence, and returns only the requested segment. It does not return an array or create additional rows. If you need every segment, rather than one selected segment, use SPLIT or combine it with FLATTEN.
For instance, given red|green|blue and the delimiter |, part 1 is red, part 2 is green, and part 3 is blue. The result is a string, so use an explicit cast if the extracted value needs to be treated as a number, date, or another data type.
Syntax and part numbering
The function takes three arguments in this order:
string: the text to split.delimiter: the exact text that marks a boundary. It can be more than one character.part_number: the position of the segment to return.
Part numbers are 1-based, which means the first segment is number 1, not 0. A part number of 0 is treated as 1. A negative number counts from the right: -1 selects the last segment, -2 the segment before it, and so on. If the requested position is outside the available segments, Snowflake returns an empty string.
Here is a compact example using a two-character delimiter:
SELECT SPLIT_PART('acct::region::team', '::', 2) AS result;
The result is region. The delimiter is the complete string ::; the function does not treat each colon as a separate delimiter.
Basic SQL examples
To extract the domain portion of an email address stored in email_address, select the second piece after splitting at the at-sign:
SELECT SPLIT_PART(email_address, '@', 2) AS email_domain
FROM customers;
For a simple path such as warehouse/stage/file.csv, the last segment can be selected with a negative index:
SELECT SPLIT_PART(file_path, '/', -1) AS file_name
FROM staged_files;
To extract the second field from a code such as US-CA-94105, use a hyphen as the delimiter:
SELECT SPLIT_PART(postal_code, '-', 2) AS state_code
FROM addresses;
These examples assume the values follow the expected structure. If a field can be missing, malformed, or contain the delimiter as ordinary data, add validation rather than assuming every returned segment is meaningful.
Choosing a delimiter and handling empty parts
The delimiter is matched as a literal string, not as a regular expression. A delimiter such as | means a vertical bar, and -- means two consecutive hyphens. This is convenient when a source format uses a distinctive multi-character separator, but it also means that a regular expression pattern cannot be passed in to match several possible separators.
Adjacent delimiters create empty segments. For example, splitting alpha||gamma on | produces alpha, an empty second segment, and gamma. A delimiter at the end also leaves an empty final segment. This behavior can matter when blank fields have a different meaning from absent fields.
If the delimiter does not occur, the whole input is the first part. Asking for a later part returns an empty string. That empty result can be difficult to distinguish from a genuinely blank segment, so check the original string or use a separate validation condition when the distinction matters.
An empty delimiter is a special case: SPLIT_PART returns the input string rather than splitting it into individual characters. The function does not trim spaces around a selected segment, either. If a source looks like east, west, the second part includes its leading space; apply TRIM when whitespace should be removed.
Using it in filters and transformations
SPLIT_PART can appear in a select list, a filter, a join condition, or another expression. For example, you can filter for rows whose final path component is manifest.json:
SELECT file_path
FROM staged_files
WHERE SPLIT_PART(file_path, '/', -1) = 'manifest.json';
You can also cast an extracted value when its textual representation is valid for the target type. For a column containing values such as order-4821, selecting the second piece returns text. A numeric comparison should use a suitable conversion, and the query should account for rows where the segment is not numeric. A cast is not a substitute for validating inconsistent source data.
If the same extraction is needed in multiple places, a common table expression can make the logic easier to read:
WITH parsed AS (
SELECT order_code,
SPLIT_PART(order_code, '-', 2) AS order_id_text
FROM incoming_orders
)
SELECT order_code, order_id_text
FROM parsed
WHERE order_id_text <> '';
This names the extracted value for later reference and makes the intent visible. Whether a query is faster depends on the surrounding workload and plan; a CTE should not be assumed to cache or materialize its result.
SPLIT_PART compared with SPLIT and FLATTEN
Choose SPLIT_PART when you know which one segment you need. It returns a single string and keeps the result at one row per input row. It is a natural fit for extracting a known position such as the second component of a code.
SPLIT is the related function for obtaining all segments as an array. To turn those array elements into separate rows, use FLATTEN. For example:
SELECT f.value::STRING AS segment, f.index
FROM TABLE(FLATTEN(INPUT => SPLIT('red|green|blue', '|'))) AS f;
This produces one row per array element; the array index exposed by FLATTEN starts at 0. That differs from SPLIT_PART, whose positive part numbering starts at 1. The distinction is a common source of off-by-one mistakes when moving between the functions.
Neither function is a full CSV parser. Delimited files can contain quoted values, escaped quotes, or delimiters inside quoted fields. Splitting a raw CSV line on a comma can therefore break a valid field into incorrect pieces. For structured files, use Snowflake’s file-format and loading features so quoting and escaping rules are handled as part of parsing.
Common mistakes and data-quality checks
- Using 0 for the first part: zero is accepted but behaves like 1. Use 1 to make the intended position clear.
- Counting from the wrong end: -1 means the last segment, while a positive index counts from the left.
- Assuming out-of-range means NULL: an unavailable part returns an empty string. Check for empty results explicitly if they indicate bad input.
- Ignoring repeated delimiters: consecutive separators create empty segments and shift the positions of later values only if the source format is not consistent.
- Expecting automatic cleanup: spaces are not removed from the returned segment. Use trimming or normalization when required.
- Treating delimiter-separated text as structured data: if values may contain the delimiter, define an escaping convention or parse the source using a format-aware method.
When inputs may be NULL, remember that a NULL argument produces a NULL result. This is different from an empty string returned for a missing segment. In a data-quality check, test NULL values and empty results separately so the two cases are not accidentally combined.
Frequently Asked Questions
Is SPLIT_PART in Snowflake 0-based or 1-based?
Positive part numbers are 1-based: 1 selects the first segment. A part number of 0 is treated as 1. Negative numbers count backward from the last segment.
What does SPLIT_PART return when the part does not exist?
It returns an empty string when the requested position is beyond the available segments. If any argument is NULL, the result is NULL instead.
Can the Snowflake delimiter contain more than one character?
Yes. The delimiter is a literal string and can contain multiple characters, such as ::. Snowflake splits at occurrences of that complete delimiter.
How do I get the last value with SPLIT_PART?
Use a part number of -1. For example, SPLIT_PART(path, '/', -1) returns the final segment of a slash-separated path.
How do I return every split value as a row?
Use SPLIT to create an array, then pass that array to FLATTEN in a table expression. Remember that FLATTEN reports array indexes from 0, unlike the positive numbering used by SPLIT_PART.
When SPLIT_PART is a good fit
Use SPLIT_PART for straightforward, consistently formatted strings when the desired segment has a known position. It is concise and readable for common tasks such as extracting a path’s final component or a fixed field from a code. For variable structures, quoted data, or cases where every segment must be examined, choose array-based processing or a format-aware parser instead. The key is to confirm the delimiter, indexing convention, and behavior for missing or blank values before relying on the extracted text.
Related
- Snowflake SPLIT_PART: Syntax, Examples & Tips
- Snowflake SPLIT_PART: Syntax, Examples, and Common Issues
- JavaScript split() Explained: Strings, Limits, 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
- Track Changes in Word: How to Update and Review EditsTo update tracked edits in Word, open the document, go to Review, turn on Track Changes for new…
- XFD Column Number in Excel: What 16,384 MeansXFD is column number 16,384 in modern Microsoft Excel. It is the last column available on an Excel…
- Share an Outlook Calendar With a QR Code: How It WorksYes—but Outlook does not generally create a QR code for sharing a calendar. You can publish a calendar…
- How to Compare Two XML Files in ExcelTo compare two XML files in Excel, import each one with Power Query, shape both into tables with…
- Excel Page Break Lines: How to View and Remove ThemExcel page break lines mark where worksheet content will be divided across printed pages. To inspect them, choose…
- Excel Hide Commands: Shortcuts for Rows, Columns, and SheetsIn Excel for Microsoft 365 on Windows, select the row or rows and press Ctrl+9 to hide them;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.