Splits a string by a delimiter and returns the field at a specified position.
Syntax
VARCHAR SPLIT_INDEX(VARCHAR str, VARCHAR sep, INT index)Arguments
Argument | Data type | Description |
str | VARCHAR | The string that you want to split. |
sep | VARCHAR | The delimiter. |
index | INT | The field position. The value starts from 0. Note If no field is obtained or the argument is set to -1, NULL is returned. |
Note
If any of arguments is NULL, NULL is returned.
Example
Test data
str(VARCHAR)
sep(VARCHAR)
index(INT)
Jack,John,Mary
,
2
Jack,John,Mary
,
3
Jack,John,Mary
NULL
0
Jack,John,Mary
,
-1
NULL
,
0
Test code
SELECT SPLIT_INDEX(str, sep, index) as var1 FROM T1;Test result
var1(VARCHAR)
Mary
NULL
NULL
NULL
NULL