Multiple values are frequently kept in one column of a MySQL database as a comma-separated list. Finding a particular value within this list, though, can be difficult.
In MySQL, the FIND_IN_SET function is available which you can use to easily search value in comma-separated values.
It works with both number and string type of list values and it is different with in() function in working.
Contents
1. Table Data
In the example, I am using department table that has the following data –
| id | dept_name | userid | username |
|---|---|---|---|
| 1 | Finance | 1,2,4,5 | yssyogesh,sonarika,anil,amit |
| 2 | IT | 1,5 | yssyogesh,amit |
| 3 | Marketing | 2,3 | sonarika,mayank |
| 4 | Operational | 2,4,5 | sonarika,anil,amit |
| 5 | Sales | 7,8,9 | vishal,jiten,shalu |
2. FIND_IN_SET Function
This function is used for searching value within the column that have comma-separated values.
It returns 0 if it doesn’t find search value otherwise returns the position of value.
You can use it both for finding number or string from the list of values.
Syntax –
FIND_IN_SET(search-value, List values)
- search-value – The first parameter is the search value, here, you can either use string value or field name.
- List-values – The second parameter is the list of values from their value is being searched, it may be comma-separated values or just single value.
# Example 1 –
SELECT FIND_IN_SET('yssyogesh','sonarika,yssyogesh,anil524,admin') as result
Output –
| result |
|---|
| 2 |
# Example 2 –
SELECT FIND_IN_SET(5,userid) as result FROM department
Output –
| result |
|---|
| 4 |
| 2 |
| 0 |
| 3 |
| 0 |
# Example 3 –
Using it in WHERE clause for searching value 1 in the userid field.
SELECT id,dept_name,userid FROM department WHERE FIND_IN_SET(1,userid)
Output –
| id | dept_name | userid |
|---|---|---|
| 1 | Finance | 1,2,4,5 |
| 2 | IT | 1,5 |
3. Difference between FIND_IN_SET and IN
Both functions are used for searching.
FIND_IN_SET
It is best when you want to find a value within a column that contains comma-separated values and It returns the position of search value in the list of values otherwise 0.
IN
It is used when to find value in the list of values and It returns 1 when it finds search value in the list of values otherwise 0.
# Example 1 – Using both of them to find value in the field which contains the comma-separated numbers.
Using FIND_IN_SET function in WHERE clause
SELECT id,dept_name,userid from department where FIND_IN_SET(2,userid)
Output –
| id | dept_name | userid |
|---|---|---|
| 1 | Finance | 1,2,4,5 |
| 3 | Marketing | 2,3 |
| 4 | Operational | 2,4,5 |
Using IN function in WHERE clause
SELECT id,dept_name,userid from department where 2 IN(userid)
Output –
| id | dept_name | userid |
|---|---|---|
| 3 | Marketing | 2,3 |
| 4 | Operational | 2,4,5 |
In the above example, using both FIND_IN_SET and IN function in WHERE clause for find value 2 in userid column which contains comma-separated ids.
In the first example, output it searches value 2 in userid and returns all rows which have search value, but in the second example, where using IN function. It only returns those rows where it finds search value in first place.
# Example 2 – Using both of them to find value in the field which contains the comma-separated text.
With FIND_IN_SET function
SELECT * FROM department where FIND_IN_SET('anil',username)
Output –
| id | dept_name | userid | username |
|---|---|---|---|
| 1 | Finance | 1,2,4,5 | yssyogesh,sonarika,anil,amit |
| 4 | Operational | 2,4,5 | sonarika,anil,amit |
With IN function
SELECT * FROM department where 'anil' in(username)
It doesn’t return any values.
4. Conclusion
FIND_IN_SET function is generally used for searching value within the comma-separated values in MySQL. IN function is also used for searching but it is not good for these types of situations.
If you found this tutorial helpful then don't forget to share.
