Categories:

String & Binary Functions (General)

SPLIT_PART

Splits a given string and returns the requested part.

If a part does not exist, an empty string is returned. If any parameter is NULL, NULL is returned.

See also

SPLIT

Syntax

SPLIT_PART(<string>, <delimiter>, <partNr>)

Arguments

string

Text to be split into parts.

delimiter

Text representing the delimiter to split by.

partNr

Requested part of the split (1-based). 0 is treated as 1. If the value is negative, the parts are counted from the right side of the string.

Usage Notes

If the string starts or is terminated with the delimiter, the system considers empty space before or after the delimiter, respectively, as a valid part of the split result.

Collation Details

Arguments with collation specifications are currently not supported.

Examples

Return the first and last parts of the localhost IP address 127.0.0.1:

SELECT SPLIT_PART('127.0.0.1', '.', 1), SPLIT_PART('127.0.0.1', '.', -1);

+---------------------------------+----------------------------------+
| SPLIT_PART('127.0.0.1', '.', 1) | SPLIT_PART('127.0.0.1', '.', -1) |
|---------------------------------+----------------------------------|
|                             127 |                                1 |
+---------------------------------+----------------------------------+

Return the first and second parts of a string of characters, separated by vertical bars; note that the space before the first vertical bar is considered to be the first part:

SELECT SPLIT_PART('|a|b|c|', '|', 1), SPLIT_PART('|a|b|c|', '|', 2);

-------------------------------+-------------------------------+
 split_part('|a|b|c|', '|', 1) | split_part('|a|b|c|', '|', 2) |
-------------------------------+-------------------------------+
                               | a                             |
-------------------------------+-------------------------------+