FIND_IN_SET
Description
Returns the position of the first occurrence of string str in the comma-separated string list strlist (counting starts from 1). This function is implemented in MySQL-compatible mode and is used to find specific strings in comma-separated value lists.
Syntax
FIND_IN_SET(<str>, <strlist>)
Parameters
| Parameter | Description |
|---|---|
<str> | The target string to search for. Type: VARCHAR |
<strlist> | The comma-separated string list to search in for str. Type: VARCHAR |
Return Value
Returns INT type, representing the position of str in strlist (counting starts from 1).
Search rules:
- Exact match: Returns position only when str exactly matches a substring in strlist
- Position counting starts from 1
- Returns the position of the first match
Special cases:
- If str is an empty string, returns 0
- If strlist is an empty string, returns 0
- If no match is found, returns 0
- If any parameter is NULL, returns NULL
- If str contains a comma, it cannot be matched correctly (because comma is the delimiter)
- Matching is case-sensitive
Examples
- Basic search
SELECT FIND_IN_SET('b', 'a,b,c');
+---------------------------+
| FIND_IN_SET('b', 'a,b,c') |
+---------------------------+
| 2 |
+---------------------------+
- Find first element
SELECT FIND_IN_SET('apple', 'apple,banana,cherry');
+------------------------------------------+
| FIND_IN_SET('apple', 'apple,banana,cherry') |
+------------------------------------------+
| 1 |
+------------------------------------------+
- Find last element
SELECT FIND_IN_SET('cherry', 'apple,banana,cherry');
+-------------------------------------------+
| FIND_IN_SET('cherry', 'apple,banana,cherry') |
+-------------------------------------------+
| 3 |
+-------------------------------------------+
- No match found
SELECT FIND_IN_SET('orange', 'apple,banana,cherry');
+--------------------------------------------+
| FIND_IN_SET('orange', 'apple,banana,cherry') |
+--------------------------------------------+
| 0 |
+--------------------------------------------+
- NULL value handling
SELECT FIND_IN_SET(NULL, 'a,b,c'), FIND_IN_SET('b', NULL);
+---------------------------+-------------------------+
| FIND_IN_SET(NULL, 'a,b,c') | FIND_IN_SET('b', NULL) |
+---------------------------+-------------------------+
| NULL | NULL |
+---------------------------+-------------------------+
- Empty string handling
SELECT FIND_IN_SET('', 'a,b,c'), FIND_IN_SET('a', '');
+-------------------------+-----------------------+
| FIND_IN_SET('', 'a,b,c') | FIND_IN_SET('a', '') |
+-------------------------+-----------------------+
| 0 | 0 |
+-------------------------+-----------------------+
- String containing comma (cannot match correctly)
SELECT FIND_IN_SET('a,b', 'a,b,c,d');
+------------------------------+
| FIND_IN_SET('a,b', 'a,b,c,d') |
+------------------------------+
| 0 |
+------------------------------+
- Case-sensitive matching
SELECT FIND_IN_SET('B', 'a,b,c'), FIND_IN_SET('b', 'A,B,C');
+---------------------------+---------------------------+
| FIND_IN_SET('B', 'a,b,c') | FIND_IN_SET('b', 'A,B,C') |
+---------------------------+---------------------------+
| 0 | 0 |
+---------------------------+---------------------------+
- Partial match will not succeed
SELECT FIND_IN_SET('ap', 'apple,banana,cherry');
+---------------------------------------+
| FIND_IN_SET('ap', 'apple,banana,cherry') |
+---------------------------------------+
| 0 |
+---------------------------------------+
- Numeric string search
SELECT FIND_IN_SET('2', '1,2,3,10,20');
+--------------------------------+
| FIND_IN_SET('2', '1,2,3,10,20') |
+--------------------------------+
| 2 |
+--------------------------------+
