To perform where clause on comma separated string/values, MySQL has an inbuilt function called FIND_IN_SET which will search for values within a comma separated values. You can also use IN operator to achieve the same but there are some limitations with IN operator which I will show below.
The FIND_IN_SET function takes two arguments and it returns the index position of the first parameter within the second parameter. We use the FIND_IN_SET function when you want to match a value with a comma-separated list of values stored in the database.
Syntax of FIND_IN_SET() function
FIND_IN_SET(search string, string list)
Parameters or Arguments
search string – The string to find.
string list – The list of string values separated by commas that is to be searched.
Notes
If string is not found in string list, the FIND_IN_SET function will return 0.
If string is NULL, the FIND_IN_SET function will return NULL.
If string list is an empty string, the FIND_IN_SET function will return 0.
If string list is NULL, the FIND_IN_SET function will return NULL.
FIND_IN_SET select examples
mysql> SELECT FIND_IN_SET('a', 'a,b,c,d');
Result: 1
mysql> SELECT FIND_IN_SET('A', 'a,b,c,d');
Result: 1
mysql> SELECT FIND_IN_SET('d', 'a,b,c,d');
Result: 4
mysql> SELECT FIND_IN_SET('e', 'a,b,c,d');
Result: 0
mysql> SELECT FIND_IN_SET('a', '');
Result: 0
mysql> SELECT FIND_IN_SET(null, 'a,b,c');
Result: NULL
mysql> SELECT FIND_IN_SET('a', null);
Result: NULL
MySQL FIND_IN_SET with WHERE CLAUSE example
coming up…
MySQL NOT FIND_IN_SET example
coming up…
MySQL FIND_IN_SET with joins example
coming up…