Search for value within a comma separated values in MySQL

In this tutorial, I will show you How to you can search a value within comma separated column in MySQL table.

I will explain about FIND_IN_SET function and difference in result when using IN and FIND_IN_SET function in SELECT query.

You can use this function when you have to find the value in a field which has a list of values which are comma separated.

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 tutorial demonstration, i am using department table which has 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 use for searching value within the column which 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 numbers 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 use for searching.

FIND_IN_SET

It is best when you want to find a value within a column which 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 a value in the list of values and It returns 1 when it find search value in the list of values otherwise 0.

# Example 1 – Using both of them for 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 for 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 comma separated values in MySQL. IN function is also use for searching but it is not good for this types of situations.

Related Post

Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *