Purpose
This function splits an input string into multiple substrings based on the delimiter and returns the substring at the specified position.
Limitations
The signs of the start_part and end_part parameters must be the same. When start_part is a negative value, you cannot specify end_part; otherwise, an error is returned.
Syntax statement
SPLIT_PART(str, delimiter, start_part[, end_part])
Parameters
str: The string to be split. Supported data types:VARCHAR,TINYTEXT,TEXT,LONGTEXT, andNULL. TheJSONdata type is not supported.delimiter: The delimiter used to split the string. Valid types:VARCHARandNULL.start_part: Specifies which part of the splitstrto return. Supported types:INTandNULL.end_part: Optional. When specified, the expression returns the string fromstart_parttoend_part. Supported types:INT,NULL.
Return type
- In most scenarios, the return value type is consistent with
str. However, whenstrisTINYTEXT, the return value type isVARCHAR. - If any parameter in the expression is
NULL, the return value isNULL. - If
delimiteris not present instrandstart_partis1, return the original string ofstr. - When
start_part = 0, treat0as1. - When
start_part < 0, the data is 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