Splits <string> at occurrences of <delimiter> and returns the <index>’th field, with 1 being the first index.
If <index> is negative, returns the abs(<index>)’th-from-last field.
If <delimiter> is empty, <string> is returned at <index> 1.
If abs(<index>) is larger than the number of fields, returns an empty string.
The function raises an error for <index> 0.
Syntax
SPLIT_PART(<string>, <delimiter>, <index>)
Parameters
| Parameter | Description | Supported input types |
|---|
<string> | The string to be split. | TEXT |
<delimiter> | The character used to split the string. | TEXT |
<index> | The index position of the substring to return within the split parts. | INTEGER |
Return Type
TEXT
Examples
Example
The following code example uses the delimiter # to split the string 'hello#world', and returns the first segment:
SELECT SPLIT_PART('hello#world', '#', 1) AS res;
Rows: 1Execution time: 5.03ms
| to split the string 'this|is|my|test', and returns the second to last segment:
SELECT SPLIT_PART('this|is|my|test', '|', -2) AS res;
Rows: 1Execution time: 8.40ms
'' to split the string 'hello world', and returns the first segment. SPLIT_PART sees the empty delimiter and interprets the entire string as the first and only part of the string and returns ‘hello world’.
SELECT SPLIT_PART('hello world', '', 1) AS res;
Rows: 1Execution time: 5.17ms
'' to split the string 'hello world', and attempts to return the seventh segment. Because there is no seventh part, SPLIT_PART returns an empty string.
SELECT SPLIT_PART('hello world', '', 7) AS res;
Rows: 1Execution time: 5.70ms
