Code Answer: Mysql query with wildcard on number strings

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,%')

From stackoverflow

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%'?
```
sydlawrence : '9%' definitely doesnt work have tried it direct into CLI, on the sun site is says that numbers dont work correctly with wildcards 9% would pick up 9 or 900 but not 9,10

sydlawrence : ah ha, I didnt use LIKE I used =

soulmerge : I'm running mysql version 5.0.70 and just tried it - it works. What type is the column?
You could try the function find_in_set
```
select find_in_set('1','1,2,3,11,12')
```
David Grayson : That's cool, I didn't know about that function.
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'
```

Code Answer