MySQL SELECT WHERE 'a' IN (`field`)

Question

Hello, I know it is not an appropriate technique to have a structure of MySQL table as such, but I have to work with such. The problem is, that the field in table has value with comma seperated integers, like "1,3,5,7,10" and I want the query to return rows, in which field has a to the query passed number in it, like:

SELECT * FROM `table` WHERE '5' IN (`field_in_table`)

However, it does not work, if, in this case, '5' is not the first number in the field. Any advises, how to solve that?

Thanks in advance, Regards, Jonas

Answer 1

You could use WHERE field_in_table LIKE '%5%' instead. Of course, the problem would be, '1,59,98' would return as wel.

Answer 2

SELECT * FROM table WHERE field_in_table LIKE '%5'");

should work

Answer 3

You could try

SELECT *
    FROM table
    WHERE '%,5,%' LIKE field_in_table OR
          '%,5'   LIKE field_in_table OR
          '5,%'   LIKE field_in_table;

A better approach might be to use regular expressions, a subject on which I am not an authority.

Answer 4

Have a look at

FIND_IN_SET

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.

Answer 5

SELECT * 
FROM table 
WHERE FIELD LIKE '%,5,%' OR 
      FIELD LIKE '5,%' OR 
      FIELD LIKE '%,5'