Search for value within a comma separated values in MySQL

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.

Search for value within a comma separated values in MySQL


Contents

  1. Table Data
  2. FIND_IN_SET Function
  3. Difference between FIND_IN_SET and IN
  4. Conclusion

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.