SPLIT_PART

Purpose

This function splits the input string into multiple substrings based on the delimiter and returns the substring at the specified position.

Limitations and considerations

The start_part and end_part parameters must use the same sign. If start_part is a negative number, you cannot specify end_part, otherwise an error will be returned.

Syntax

SPLIT_PART(str, delimiter, start_part[, end_part])

Parameters

• str: the string to be split. Supported types: VARCHAR, TINYTEXT, TEXT, LONGTEXT, and NULL. The JSON type is not supported.
• delimiter: the delimiter used to split the string. Supported types: VARCHAR and NULL.
• start_part: the position of the substring to be returned after splitting str. Supported types: INT and NULL.
• end_part: optional. If specified, the expression returns the substring from start_part to end_part. Supported types: INT and NULL.

Return type

• In most cases, the return type is the same as the type of str. However, if str is of the TINYTEXT type, the return type is VARCHAR.
• If any parameter of the expression is NULL, the return value is NULL.
• If the delimiter does not exist in str and start_part is 1, the original string of str is returned.
• If start_part is 0, it is treated as 1.
• If start_part is less than 0, the substrings are processed in reverse order.
• Negative numbers are processed in reverse order.

Examples

SELECT
    SPLIT_PART('a,b,c,d', ',', 1),
    SPLIT_PART('a,b,c,d', ',', 1, 2),
    SPLIT_PART('a,b,c,d', ',', 0),
    SPLIT_PART('a,b,c,d', ',', -2),
    SPLIT_PART('a,b,c,d', ',', 5),
    SPLIT_PART('a,b,c,d', ',', -5);

The return result is as follows:

+-------------------------------+----------------------------------+-------------------------------+--------------------------------+-------------------------------+--------------------------------+
| SPLIT_PART('a,b,c,d', ',', 1) | SPLIT_PART('a,b,c,d', ',', 1, 2) | SPLIT_PART('a,b,c,d', ',', 0) | SPLIT_PART('a,b,c,d', ',', -2) | SPLIT_PART('a,b,c,d', ',', 5) | SPLIT_PART('a,b,c,d', ',', -5) |
+-------------------------------+----------------------------------+-------------------------------+--------------------------------+-------------------------------+--------------------------------+
| a                             | a,b                              | a                             | c                              |                               |                                |
+-------------------------------+----------------------------------+-------------------------------+--------------------------------+-------------------------------+--------------------------------+
1 row in set