Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL SPLIT_PART

The `SPLIT_PART` function in PostgreSQL is used to split a string into parts based on a specified delimiter and returns the specified part. It is a useful function for parsing and extracting specific segments of a string.

Usage

The `SPLIT_PART` function is employed when you need to retrieve a specific portion of a string that is separated by a delimiter. It is especially useful for handling structured strings like CSV data.

sql
SPLIT_PART(string, delimiter, field)

In this syntax, `string` and `delimiter` should be of the `text` data type or implicitly castable to `text`. `string` is the source string to be split, `delimiter` is the character or string that separates the parts, and `field` is the 1-based index of the part to return. The function returns a `text` type.

Examples

1. Basic SPLIT_PART Usage

sql
SELECT SPLIT_PART('apple,banana,cherry', ',', 2);

This example extracts the second element, `banana`, from the comma-separated list.

2. Extracting Domain from Email

sql
SELECT SPLIT_PART('user@example.com', '@', 2);

Here, the function retrieves the domain part, `example.com`, from the email address.

3. Parsing Version Numbers

sql
SELECT SPLIT_PART('Version 10.2.5', '.', 3);

This example extracts the third segment, `5`, from a version number string, demonstrating its application in parsing version numbers. If the specified field index is out of range, the function will return an empty string.

4. Extracting the Last Part of a String

sql
SELECT SPLIT_PART('file.tar.gz', '.', 3);

To extract the last part of a string, you can calculate the number of parts first and then use `SPLIT_PART` to retrieve the last part.

Tips and Best Practices

  • Ensure the delimiter exists. Before using `SPLIT_PART`, verify that the delimiter is present in the string to avoid unexpected results. If the delimiter is not found, the entire string is returned as the first field.
  • Use with structured strings. Ideal for strings with consistent delimiters, such as CSV or log entries.
  • Mind the index. The `field` argument is 1-based, not 0-based, so be careful when specifying which part to extract.
  • Handle missing parts. Be prepared for `SPLIT_PART` to return an empty string if the requested part does not exist.
  • Understand limitations. `SPLIT_PART` does not support negative indices, unlike some other languages.
  • Performance considerations. `SPLIT_PART` is not suitable for delimiter-based parsing of very large text data due to potential performance implications.