Purpose
This function splits the input string into multiple substrings based on the delimiter and returns the substring at the specified position.
Note
This function is supported starting from OceanBase Database V4.3.5 BP1.
Limitations
The start_part and end_part parameters must use the same symbol. If start_part is a negative number, you cannot specify end_part, otherwise an error will be reported.
Syntax
SPLIT_PART(str, delimiter, start_part[, end_part])
Parameters
str: the string to be split. Supported types:VARCHAR,TINYTEXT,TEXT,LONGTEXT, andNULL.JSONis not supported.delimiter: the delimiter used to split the string. Supported types:VARCHARandNULL.start_part: the part to return from the split string. Supported types:INTandNULL.end_part: optional. If specified, the expression returns the substring fromstart_parttoend_part. Supported types:INTandNULL.
Return type
- In most cases, the return type is the same as the type of
str, except whenstrisTINYTEXT, in which case the return type isVARCHAR. - If any parameter of the expression is
NULL, the result isNULL. - If
delimiterdoes not exist instrandstart_partis1, the original string ofstris returned. - If
start_part = 0, it is treated as1. - If
start_part < 0, it 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