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.

Collation:Not supported
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.

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                             |
-------------------------------+-------------------------------+