Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set the 'where' clause on a field with comma-seperated values?

Tags:

sql

select

mysql

I have a db table with a field with values stored in the value1,value2,value3,value4 format. I want to find all the rows where this field contains a defined value, eg. value3.

How can I perform a query to search a value in a field like this one?

like image 416
Rodrigo Balest Avatar asked Sep 02 '25 06:09

Rodrigo Balest


2 Answers

use FIND_IN_SET()

SELECT  *
FROM    tableName
WHERE   FIND_IN_SET('value3', 'comma separated value here') > 0
  • SQLFiddle Demo

SOURCE

  • MySQL FIND_IN_SET

Description from MySQL Docs:

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL.

like image 126
John Woo Avatar answered Sep 04 '25 18:09

John Woo


You can do this using like:

where concat(',', field, ',') like '%,value3,%'
like image 41
Gordon Linoff Avatar answered Sep 04 '25 20:09

Gordon Linoff