How can I search within a table of comma-separated values?

Question

I have a MySQL table which contains comma-separated values like this:

first row=(3,56,78,12)  
second row=(6,44,2,3)  
third row=(67,4,2,7,1)  
fourth row=(88,55,22,33)  
fifth row=(88,55,3,1,5)

I want to select the rows which have 3 in their set. How can I do this?

Answer 1

Try:

SELECT * FROM TABLE_NAME WHERE FIND_IN_SET( 3, COLUMN_NAME ) 

Answer 2

How about something like

SELECT *
FROM Table
WHERE Field LIKE '3,%'
OR Field LIKE '%,3'
OR Field LIKE '%,3,%'
OR Field = '3'

Answer 3

Just Use Mysql Function FIND_IN_SET(str,strlist) .

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. This function does not work properly if the first argument contains a comma (“,”) character.

 SELECT * FROM table_name WHERE FIND_IN_SET('3', column_name);

Answer 4

use this condition

WHERE firstrow LIKE '3,%' 
    OR firstrow like '%,3'
    OR firstrow like '3'

Answer 5

SELECT *
FROM Table
WHERE Field IN ($first_row)
OR Field IN ($second_row)
OR Field IN ($third_row)
OR Field IN ($fourth_row);

Answer 6

you can use IN statement in your query.Please try this.

SELECT *
FROM Table
WHERE Field IN ($first_row)
OR Field IN ($second_row)
OR Field IN ($third_row)
OR Field IN ($fourth_row);