MySQL | FIND_IN_SET method
Start your free 7-days trial now!
MySQL's FIND_IN_SET(~) method returns the position of the first occurrence of a string in a string list.
Parameters
1. str | string
The string we are trying to find in strlist.
2. strlist | string list
A string list composed of substrings separated by commas.
Return value
Case | Return value |
|---|---|
| Position of first occurrence of |
| 0 |
Empty | 0 |
NULL argument |
Examples
Basic usage
To return position of 'b' in the string list 'a,b,c,d':
SELECT FIND_IN_SET('b','a,b,c,d');
+----------------------------+| FIND_IN_SET('b','a,b,c,d') |+----------------------------+| 2 |+----------------------------+
'b' is the second substring in the string list 'a,b,c,d'.
To return position of 'b' in the string list 'a,b,c,d,b':
SELECT FIND_IN_SET('b','a,b,c,d,b');
+------------------------------+| FIND_IN_SET('b','a,b,c,d,b') |+------------------------------+| 2 |+------------------------------+
Despite there being two occurrences of 'b' in the string list, we return the position of the first occurrence.
No occurrence in strlist
To return position of 'b' in the string list 'a,c,d':
SELECT FIND_IN_SET('b','a,c,d');
+--------------------------+| FIND_IN_SET('b','a,c,d') |+--------------------------+| 0 |+--------------------------+
0 is returned as 'b' does not exist in string list 'a,c,d'.
Empty strlist
To return position of 'b' in the string list '':
SELECT FIND_IN_SET('b','');
+---------------------+| FIND_IN_SET('b','') |+---------------------+| 0 |+---------------------+
0 is returned as 'b' does not exist in string list ''.
NULL argument
To return position of 'b' in the string list NULL:
SELECT FIND_IN_SET('b', NULL);
+------------------------+| FIND_IN_SET('b', NULL) |+------------------------+| NULL |+------------------------+
NULL is returned as one of the arguments is NULL.
