Use FIND_IN_SET to find the position of a substring in a delimited string list - MaxCompute

Returns the 1-based position of a string within a comma-separated (or custom-delimited) list of strings.

Syntax

BIGINT FIND_IN_SET(STRING <str1>, STRING <str2>[, STRING <delimiter>])

Parameter	Required	Description
str1	Yes	The string to search for.
str2	Yes	A string containing substrings separated by `delimiter`.
delimiter	No	The delimiter used to split `str2`. Defaults to a comma (`,`). Can be a single character or a multi-character string.

Returns a BIGINT value:

Positive integer (1-based): The position of str1 in str2. The first position is 1.
0: Returned when str1 is not found in str2, or when str1 contains the delimiter.
NULL: Returned when either str1 or str2 is NULL.

Example 1: ab exists in the list — returns its position.

SELECT FIND_IN_SET('ab', 'abc,hello,ab,c') AS pos;

Result:

+------------+
| pos        |
+------------+
| 3          |
+------------+

Example 2: hi does not exist in the list — returns 0.

SELECT FIND_IN_SET('hi', 'abc,hello,ab,c') AS pos;

Result:

+------------+
| pos        |
+------------+
| 0          |
+------------+

Example 3: Custom delimiter (underscore) — ab is found at position 3.

SELECT FIND_IN_SET('ab', 'abc_hello_ab_c', '_') AS pos;

Result:

+------------+
| pos        |
+------------+
| 3          |
+------------+

Example 4: str1 is NULL — returns NULL.

SELECT FIND_IN_SET(null, 'abc,hello,ab,c') AS pos;

Result:

+------------+
| pos        |
+------------+
| NULL       |
+------------+

FIND_IN_SET is a string function. For related functions, see String functions.