Having a number in

Question

Can someone give me a query that will return as a result rows ID 1 & 3?

ID Name Hidden 
1 Mika 1,4,2 
2 Loca 0 
3 Nosta 4 
4 Like 2 

Something like this SELECT * FROM table WHERE Hidden HAVING(4)

Answer 1

SELECT * FROM table WHERE Hidden LIKE '%4%'

the % are wildcards.

Answer 2

Full Text Search might be a reasonable solution for this as long as you use the correct word breaks.

Answer 3

SELECT * FROM table WHERE FIND_IN_SET('4',Hidden);

docs for FIND_IN_SET

Answer 4

Either go with Full Text Search, as suggested, or

Spin the Hidden values off into a separate table, with the ID of current row.

Eg, Mika would have three entries in this table

ID = 1, Hidden =1
ID = 1, Hidden =4
ID = 1, Hidden =2

Then you could return results against this spin off table.

Answer 5

SELECT * FROM table WHERE CONCAT(',',Hidden,',') LIKE '%,4,%'

or you can avoid using LIKE like this

SELECT * FROM table WHERE INSTR(CONCAT(',',Hidden,','), ',4,') > 0

this will not get things like 40, 14, etc, but you need to make sure there are no spaces in the Hidden field (eg, 1, 4, 5 or update the concat and LIKE function accordingly.

Answer 6

You may also want to consider normalizing the table and storing these "hidden" values in a separate table with an index on the apropriate column. Depending on the number of rows you have that would be much faster:

ID Hidden
1  1
1  4
1  2 
3  4 
4  2 

and:

SELECT DISTINCT table.* FROM table, hidden_table WHERE table.ID = hidden_table.ID AND hidden_table.hidden = 4