SQL SPLIT_PART Function: Syntax, Examples, and Alternatives

Formulas & functions · Updated November 22, 2026

SPLIT_PART is a SQL function that returns one part of a string separated by a chosen delimiter. In PostgreSQL and Snowflake, for example, SPLIT_PART('red|green|blue', '|', 2) returns green; whether the function is available and how its edge cases work depend on your database.

What SPLIT_PART means

The name describes the operation: split a string wherever a delimiter occurs, then return the requested part. A delimiter is the character or sequence of characters between fields. In north/south/east, the slash is the delimiter and south is the second part. The function is useful when a column contains structured text, such as a code with sections, a file path, or a delimited label.

Despite the name, SPLIT_PART generally returns one value, not a table of all the pieces. It is therefore convenient when a query needs a specific component, such as the second segment of a product code. If you need every component as a separate row, a row-producing split function or a proper relational design is usually a better fit.

There is no single SQL standard function named SPLIT_PART that works identically across every database. PostgreSQL and Snowflake support it, while other systems provide different functions or require a workaround. Check the documentation for the database engine and version behind your query.

Syntax and argument order

In PostgreSQL and Snowflake, the common form is:

SPLIT_PART(string, delimiter, part_number)

  • string is the text to divide.
  • delimiter is the separator to search for. It can be more than one character.
  • part_number identifies which piece to return. In PostgreSQL, positive part numbers start at 1: 1 means the first part, 2 the second, and so on.

For example, in a query that reads values from a table called items, SELECT SPLIT_PART(item_code, '-', 2) FROM items; returns the section after the first hyphen and before the next one. If item_code contains AB-42-blue, the result is 42.

The arguments have a fixed order: the string comes first, the delimiter second, and the requested part number third. Reversing the delimiter and number is a frequent source of errors. The delimiter is text, so write it as a quoted string literal, such as '-' or '::'.

Examples in PostgreSQL

These examples use PostgreSQL syntax. Suppose a table stores a location in region_code with values such as west:CA:oakland. To select the second field:

SELECT SPLIT_PART(region_code, ':', 2) AS state_code FROM locations;

For that sample value, the query returns CA. To return the final field when the number of segments can vary, PostgreSQL also accepts a negative part number. With west:CA:oakland, SPLIT_PART(region_code, ':', -1) returns oakland. This negative-index behavior is not safe to assume in every database that has a similarly named function, so verify it for your engine.

A multi-character delimiter works too. If a value is team::sales::active, then SPLIT_PART(value, '::', 2) returns sales. The function searches for the full delimiter, not just its first character.

You can also use the function in a filter. To find rows whose second hyphen-separated segment is 42, use WHERE SPLIT_PART(item_code, '-', 2) = '42'. This can be handy for exploration or occasional queries. For a frequently used condition on a large table, consider storing the component separately or using a database-supported index strategy; applying a function to a column can affect whether a normal index is usable.

Missing parts, empty fields, and NULL

Splitting does not automatically validate that a value has the structure you expect. In PostgreSQL, requesting a positive part number beyond the available fields returns an empty string. Thus, if code is A-B, asking for part 3 does not produce an error; it produces an empty result. An empty string is different from SQL NULL, so comparisons and reporting may treat the two differently.

Repeated delimiters can create empty fields. For example, A--C contains an empty second segment when split on a hyphen. A trailing delimiter, as in A-B-, can likewise mean the last part is empty. This distinction matters when empty fields indicate bad data, an intentionally blank value, or a missing component.

If the input string is NULL, PostgreSQL returns NULL. A NULL delimiter or part number also yields NULL under PostgreSQL’s strict-function behavior. To display a fallback for a missing input, you can use COALESCE(SPLIT_PART(code, '-', 2), 'unknown'), but remember that this only replaces NULL; it does not replace an empty string. Use NULLIF if you deliberately want to treat an empty result as NULL, and validate the input format when malformed values need to be reported rather than silently accepted.

How it differs across SQL databases

Before copying a query, identify the database. Similar-looking functions are not always interchangeable:

  • PostgreSQL: Provides SPLIT_PART(string, delimiter, field), including negative field numbers that count from the end.
  • Snowflake: Provides SPLIT_PART(string, delimiter, partNumber). Check Snowflake’s documentation for its own rules for zero, negative values, and edge cases rather than assuming PostgreSQL behavior.
  • SQL Server: Does not provide a scalar function with this exact name. STRING_SPLIT returns rows rather than one selected part, and result ordering should not be assumed unless using a supported ordinal option in an appropriate SQL Server version.
  • MySQL: Does not have a built-in function named SPLIT_PART. SUBSTRING_INDEX can extract text around a specified number of delimiter occurrences, but its behavior and purpose differ from selecting a numbered field directly.
  • BigQuery: SPLIT returns an array. Use an array index such as OFFSET to select an element; BigQuery array offsets start at 0, unlike PostgreSQL’s positive part numbers, which start at 1.

These differences can matter during a database migration. A query that returns the last field in PostgreSQL using -1 may need a different expression elsewhere, and a function that returns rows cannot always replace one that returns a scalar without changing the query’s shape.

Choosing between SPLIT_PART and alternatives

Use SPLIT_PART when you need one known segment and the source text follows a dependable delimiter format. It keeps a simple query readable. For instance, extracting a fixed section from a well-defined internal code is a reasonable use.

Choose another approach when the task is different:

  • Need all segments as rows: Use a table-valued or row-generating split function available in your database. This changes one input row into multiple output rows.
  • Need all segments as an array: Use the engine’s array-producing split function, then select or process array elements.
  • Need text before or after a known marker: A substring or position function may express the goal more clearly and can behave better when the number of segments varies.
  • Need to parse complex formats: Delimited splitting is not a substitute for parsing quoted CSV, JSON, or another structured format. Use the relevant parser or data type so that embedded delimiters and escaping are handled correctly.

For example, splitting a comma-separated string does not correctly parse every CSV value: a quoted field may itself contain a comma. If values are being imported from a file, parse the file according to its format before relying on individual components.

Common problems and reliable practices

One common mistake is assuming the delimiter is always present. In PostgreSQL, if it is absent, the whole string is the only field; requests for later positive fields return an empty string. That may look like a valid but blank component, so test representative malformed values rather than relying only on clean examples.

Another problem is confusing an empty result with a missing row or a NULL. Check all three cases in your data: NULL input, an empty string, and a string with fewer delimiters than expected. If a component is required, make that requirement explicit in validation or in a conditional expression.

Also consider whitespace. SPLIT_PART('A, B', ',', 2) returns a value that includes the space before B. If the data may contain spaces around fields, trim the result using the trimming function supported by your database. Do not trim blindly if spaces are meaningful in the source.

Finally, splitting at query time can conceal a design issue. If applications regularly filter, join, or group by a component, separate columns may be clearer and easier to validate. Splitting is useful for reading existing text, but storing multiple independent facts in one column can make constraints and indexing less straightforward.

Frequently Asked Questions

Is SPLIT_PART a standard SQL function?
No. It is available in databases such as PostgreSQL and Snowflake, but support and behavior vary by engine. Check the documentation for the system running your query.

Does SPLIT_PART start counting at 0 or 1?
In PostgreSQL, positive field numbers start at 1, so the first part is 1. Other systems may use different conventions; BigQuery, for example, uses zero-based array offsets after splitting.

What happens if the delimiter is not found?
In PostgreSQL, the entire input is the first field. A request for a later positive field returns an empty string. Other databases may have different rules.

Can SPLIT_PART return the last segment?
PostgreSQL supports a negative field number, such as -1, to count from the end. Confirm support and exact behavior before using that pattern in another database.

Does SPLIT_PART split a column into multiple rows?
No. It returns one selected part for each input value. To produce multiple rows, use a row-returning split function or an equivalent database-specific method.

Can I use SPLIT_PART on comma-separated CSV data?
Only when commas truly separate fields and cannot appear inside quoted values. For real CSV, use a CSV-aware parser; a simple delimiter split can misread quoted commas.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.