Skip to content

MySQL – Where clause on a comma separated values/String

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…

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments