mysql string searching algorithm query help

i have a string table in my mysql/php table.

 id | str  

 3  |  2,4,5,6
 4  |  7,8,9,14
 5  |  3,1,16

if i search with LIKE keyword for example "1" the result is below and its wrong

 id | str  

 4  |  7,8,9,14
 5  |  3,1,16

But the expected result is

 id | str  
 5  |  3,1,16

since "1" is only in the开发者_JAVA技巧 above row but unfortunately no idea,

help me with this,

Nithish.

---

what you're looking for is FIND_IN_SET function

  select find_in_set('1', '3,2,1');  => 3
  select find_in_set('1', '3,2,14'); => 0

better yet, consider normalizing your database

---

use REGEXP instead of LIKE http://dev.mysql.com/doc/refman/5.1/en/regexp.html

str REGEXP '1[,$]'

---

you should use REGEXP instead of LIKE
select * from mytable WHERE str REGEXP '(^|,)1(,|$)'
^ stands for "beginning of the string" and $ for "end of the string"
see http://dev.mysql.com/doc/refman/5.1/en/regexp.html for more details