mysql string searching algorithm query help

Question

Hi,

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 above row but unfortunately no idea,

help me with this,

Nithish.

Answer 1

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

str REGEXP '1[,$]'

Answer 2

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

Answer 3

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