SPLIT_PART
Description
The SPLIT_PART function splits a string into multiple parts according to the specified separator and return one of the parts.
Syntax
SPLIT_PART ( <str>, <separator>, <part_index> )
Parameters
| Parameter | Description |
|---|---|
<str> | The string to be split |
<separator> | The string used for splitting |
<part_index> | The index of the part to be returned. Starting from 1 |
Return Value
Returns the specified part of the string split according to the delimiter. Special cases:
- If any of the parameters is NULL, NULL is returned.
- When
<part_index>is 0, NULL is returned.
Examples
- Basic string splitting
SELECT SPLIT_PART('hello world', ' ', 1);
+----------------------------------+
| SPLIT_PART('hello world', ' ', 1) |
+----------------------------------+
| hello |
+----------------------------------+
- Get second part
SELECT SPLIT_PART('apple,banana,cherry', ',', 2);
+-------------------------------------------+
| SPLIT_PART('apple,banana,cherry', ',', 2) |
+-------------------------------------------+
| banana |
+-------------------------------------------+
- Index is 0 (returns NULL)
SELECT SPLIT_PART('apple,banana,cherry', ',', 0);
+-------------------------------------------+
| SPLIT_PART('apple,banana,cherry', ',', 0) |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
- Negative index (count from end)
SELECT SPLIT_PART('apple,banana,cherry', ',', -1), SPLIT_PART('apple,banana,cherry', ',', -2);
+--------------------------------------------+--------------------------------------------+
| SPLIT_PART('apple,banana,cherry', ',', -1) | SPLIT_PART('apple,banana,cherry', ',', -2) |
+--------------------------------------------+--------------------------------------------+
| cherry | banana |
+--------------------------------------------+--------------------------------------------+
- Index out of range
SELECT SPLIT_PART('apple,banana', ',', 5), SPLIT_PART('apple,banana', ',', -5);
+-----------------------------------+------------------------------------+
| SPLIT_PART('apple,banana', ',', 5) | SPLIT_PART('apple,banana', ',', -5) |
+-----------------------------------+------------------------------------+
| | |
+-----------------------------------+------------------------------------+
- NULL value handling
SELECT SPLIT_PART(NULL, ',', 1), SPLIT_PART('test', NULL, 1), SPLIT_PART('test', ',', NULL);
+---------------------------+-----------------------------+-------------------------------+
| SPLIT_PART(NULL, ',', 1) | SPLIT_PART('test', NULL, 1) | SPLIT_PART('test', ',', NULL) |
+---------------------------+-----------------------------+-------------------------------+
| NULL | NULL | NULL |
+---------------------------+-----------------------------+-------------------------------+
- Empty string handling
SELECT SPLIT_PART('', ',', 1), SPLIT_PART('test', '', 2);
+------------------------+---------------------------+
| SPLIT_PART('', ',', 1) | SPLIT_PART('test', '', 2) |
+------------------------+---------------------------+
| NULL | |
+------------------------+---------------------------+
- Separator doesn't exist
SELECT SPLIT_PART('hello world', '|', 1), SPLIT_PART('hello world', '|', 2);
+-----------------------------------+-----------------------------------+
| SPLIT_PART('hello world', '|', 1) | SPLIT_PART('hello world', '|', 2) |
+-----------------------------------+-----------------------------------+
| NULL | NULL |
+-----------------------------------+-----------------------------------+
- Consecutive separators
SELECT SPLIT_PART('a,,c', ',', 1), SPLIT_PART('a,,c', ',', 2), SPLIT_PART('a,,c', ',', 3);
+----------------------------+----------------------------+----------------------------+
| SPLIT_PART('a,,c', ',', 1) | SPLIT_PART('a,,c', ',', 2) | SPLIT_PART('a,,c', ',', 3) |
+----------------------------+----------------------------+----------------------------+
| a | | c |
+----------------------------+----------------------------+----------------------------+
- UTF-8 character handling
SELECT SPLIT_PART('ṭṛì ḍḍumai ṭṛì', ' ', 2);
+--------------------------------------+
| SPLIT_PART('ṭṛì ḍḍumai ṭṛì', ' ', 2) |
+--------------------------------------+
| ḍḍumai |
+--------------------------------------+
Keywords
SPLIT_PART, SPLIT
