Mysql query with wildcard on number strings

Question

I am trying to query a mysql table which contains strings of numbers (i.e. '1,2,3,4,5').

How do I search to see if it has '1' but not '11' bearing in mind if it is '9,10' '9%' doesnt work??

Fixed!

(field like '10' OR field like '%,10,%' OR field like '%,10' OR field like '10,%')

Answer 1

You need the function FIND_IN_SET. Btw, '9%' should work, if the column contains the values you specified, are you sure you're querying

SELECT * FROM table WHERE field LIKE '9%'?

Answer 2

You could try the function find_in_set

select find_in_set('1','1,2,3,11,12')

Answer 3

Standard SQL can do it as well:

...
WHERE 
  ',' + SetValue + ',' LIKE '%,1,%' 
  AND ',' + SetValue + ',' NOT LIKE '%,11,%'

This expression cannot make use of an index, therefore performance will degrade quickly as the table size rises.

For better performance your table should be properly normalized, e.g.

SetId  SetValue
    1  1
    1  2
    1  3
    1  4
    1  5

instead of

SetId  SetValue
    1  '1,2,3,4,5'