FIND_IN_SET command syntax, parameters, and return value - MaxCompute

FIND_IN_SET returns the position of the string str1 within the string str2, which is separated by delimiter. The first position is 1.

Syntax

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

Parameters

Parameter	Required	Description
str1	Yes	A value of the STRING type. This parameter specifies the substring that you want to search for.
str2	Yes	A value of the STRING type. This parameter specifies a string in which substrings are separated by delimiter.
delimiter	No	A value of the STRING type. The default delimiter is a comma (,). The delimiter can be a single character or a string.

Return value

The function returns a BIGINT value based on the following conditions:

If str1 cannot be found within str2 or if str1 includes the delimiter, the return value is 0.
If either str1 or str2 is NULL, the function returns NULL.

Examples

Example 1: To find the position of the string ab in the string abc,hello,ab,c, separated by a comma (,), use the following command:
```
SELECT FIND_IN_SET('ab', 'abc,hello,ab,c') AS pos;
```
The following result is returned:
```
+------------+
| pos        |
+------------+
| 3          |
+------------+
```
Example 2: To find the position of the string hi in the string abc,hello,ab,c, separated by a comma (,), use the following command:
```
SELECT FIND_IN_SET('hi', 'abc,hello,ab,c') AS pos;
```
The following result is returned:
```
+------------+
| pos        |
+------------+
| 0          |
+------------+
```
Example 3: To find the position of the string ab in the string abc_hello_ab_c, separated by an underscore (_), use the following command:
```
SELECT FIND_IN_SET('ab', 'abc_hello_ab_c', '_') AS pos;
```
The following result is returned:
```
+------------+
| pos        |
+------------+
| 3          |
+------------+
```

Example 4: When the input parameter str1 or str2 is NULL, use the following command:

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

The following result is returned:

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

Related functions

The FIND_IN_SET function is categorized as a string function. For more information on string functions related to search and format transformation, see String functions.