Use SPLIT_INDEX to split a string and return a field - Realtime Compute for Apache Flink

Splits a string by a delimiter and returns the element at the specified zero-based position. Returns NULL if any argument is NULL, the index is out of range, or the index is -1.

Syntax

VARCHAR SPLIT_INDEX(VARCHAR str, VARCHAR sep, INT index)

Arguments

Argument	Data type	Description
`str`	VARCHAR	The string to split.
`sep`	VARCHAR	The delimiter.
`index`	INT	The zero-based position of the element to return. Returns NULL if the index is out of range or -1.

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

Result explanation

Row 1: "Jack,John,Mary" split by "," gives ["Jack", "John", "Mary"]. Index 2 returns "Mary".
Row 2: Index 3 is out of range (the array has indices 0–2), so the result is NULL.
Row 3: sep is NULL, so the result is NULL.
Row 4: Index is -1, so the result is NULL.
Row 5: str is NULL, so the result is NULL.