In most cases, finding values in a comma-separated string becomes necessary. You can do the same job with PHP or any other language but doing it directly in MySql is easy and fast. In this article, you’ll learn about “Finding Values in Comma Separated String Using MySQL” with the explanation. So don’t leave this page and keep reading. So first let’s identify the problem.

The problem regarding finding values in comma separated string using MySql

Let’s assume that we have a hobbies field in a user table where we stored the user’s hobbies. Now we have a select box and we have to select one hobby and we will list all the users who have selected a hobby. This task can be done via programming language but we will do it via MySql query.

How to find values in comma separated string using MySql?

Here is a solution. As you know that we need to get the records that match with the given string in the field column via MySql so we will use some function that will solve our problem. Fortunately, there is a nice function FIND_IN_SET() that can do the job.

FIND_IN_SET() function will match comma-separated values. It returns the position of a string value if it is available (as a substring) within a string. It returns 0 when the search string does not exist in the string. Syntax of this function is here:

FIND_IN_SET(searchstring,commaseperatedstring)

Here searchstring is a string to be searched eg: ‘gardening’ or ‘gardening’,’ sports’ and commaseperatedstring string is comma-separated string or field in MySQL table. Now we will write some queries as an example.

Query Examples to find value

//Example 1
SELECT FIND_IN_SET('gardening',hobbies);
 
// hobbies is the field name which has comma separated string

//Example 2
SELECT username FROM tblUser WHERE FIND_IN_SET('sports',hobbies) > 0;
 
Output
// it will return the name of the user who has sports as his hobbies

Above simple and easy to understand example will solve the problem and required users. Now you can perform any action that you required.

I hope you understand that what I explained. However, if you still need help then feel free to leave comment in below box or just send me message HERE.

Here are some other tutorials regarding PHP language that can help to learn more and more:

Calculating Years, month and days in PHP

Comma separated values in PHP