Search code examples
phpmysqlsql-like

MySQL LIKE limiting

Basicly I have a row in my table, that has the following format for it's value - 1,2,3,4,10,11,21,34 etc..

I'm retrieving values with LIKE statement, but since I'm using it with %<value>%, when searching for 1 it returns 11, 21, 1 and so on.

How can I limit it, to return values based on one?

Solution

  • Instead of col LIKE '%<value>%' do

    col LIKE '%,<value>,%' OR col LIKE '<value>,%' OR col LIKE '%,<value>' OR col LIKE '<value>'

    or better yet

    col REGEXP '(.+,|^)<val>(,.+|$)'

    But the best solution is to change your data structure! Either use the SET type, or several fields, or a join table.