FIND_IN_SET in mysql with where condition with example

The FIND_IN_SET function in MySQL is used to search for a specific value within a comma-separated list of values. It returns the position of the value within the list, or 0 if the value is not found.

Here’s an example of how to use FIND_IN_SET with a WHERE condition:

Suppose you have a table named “students” with the following columns: “id”, “name”, and “favorite_subjects”.

+----+----------+---------------------+| id | name     | favorite_subjects   |+----+----------+---------------------+| 1  | John     | Math,Science        || 2  | Jane     | History,English     || 3  | Bob      | Math,History        || 4  | Sarah    | Science,English     |+----+----------+---------------------+

If you want to find all students who have “Math” as one of their favorite subjects, you can use the following query:

SELECT * FROM studentsWHERE FIND_IN_SET('Math', favorite_subjects) > 0;

This query will produce the following result:

+----+-------+--------------------+| id | name  | favorite_subjects  |+----+-------+--------------------+| 1  | John  | Math,Science       || 3  | Bob   | Math,History       |+----+-------+--------------------+

In this example, the FIND_IN_SET function is used within the WHERE condition to search for the value “Math” within the “favorite_subjects” column. The condition “FIND_IN_SET(‘Math’, favorite_subjects) > 0” returns true for any row where “Math” is found within the comma-separated list.

The query returns all rows where the condition is true, which includes John and Bob in this example.