Search for value within a comma separated values in MySQL

Sometimes, in a MySQL database, people store many values together in one column, separated by commas. But searching for a specific value in this kind of list can be tricky.

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.

1. Table Data which use for searching

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 gives back 0 if the search value isn’t found; otherwise, it shows the position of the 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

These two functions are used for searching, but they work a bit differently.

FIND_IN_SET

Perfect for locating a value in a column with comma-separated values.
Returns the position of the search value in the list, or 0 if not found.

Used to find a value in a list of values.
Returns 1 if it finds the search value in the list, or 0 otherwise.

# Example 1 – Using both of them to find value in the field that 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 example above, both the FIND_IN_SET and IN functions are used in the WHERE clause to find the value 2 in the userid column, which has comma-separated IDs.

In the first example, output it searches value 2 in userid and returns all rows that have search value, but in the second example, where using IN function. It only returns those rows where it finds the search value in the first place.

# Example 2 – Using both of them to find value in the field that 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

When tackling MySQL databases with comma-separated values, the FIND_IN_SET function stands out as a versatile tool for efficient searches, accommodating both numbers and strings.

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.