Alternative to LIKE clause in Mysql

Question

I have got a filed in my database table which stores categories. I am storing the categories in the following format:

1,12,15

Now when I try to search for a product from category 1, I use LIKE clause in my query such as

where (prod_catg LIKE %1,% or prod_catg LIKE %1% or prod_catg LIKE %,1% )

This returns me the products from all the three categories 1,12 and 15. Instead I just want the products from category 1.

I have also tried IN clause but no results found.

Can anyone please suggest me some other alternative.

Answer 1

prod_catg LIKE '1,%' --matches when 1 is the first category
OR prod_catg LIKE '%,1,%' --matches when 1 is somewhere in the middle
OR prod_catg LIKE '%,1' --matches 1 when is the last category

anyway you had better to refactor your schema by adding a category table and the reference to it on the product (main) table

EDIT

another way to face this problem is using REGEXP which will lead to a shorter WHERE clause (here is what i've used to test):

DECLARE @regexp VARCHAR(100);
SET @regexp = '^1,.*|.*,1$|.*,1,.*';

SELECT
    '1,11,15,51,22,31' REGEXP @regexp AS test1,
    '51,11,15,1,22,31' REGEXP @regexp AS test2,
    '11,15,51,22,31,1' REGEXP @regexp AS test3,
    '7,11,15,51,22,31' REGEXP @regexp AS test4,
    '51,11,15,7,22,31' REGEXP @regexp AS test5,
    '11,15,51,22,31,7' REGEXP @regexp AS test6;

this will match your prod_catg against the Regular Expression '^1,.*|.*,1$|.*,1,.*' returnig 1 (TRUE) if it matches, 0 (FALSE) otherwise.

Then your WHERE clause will look like:

WHERE prod_catg REGEXP '^1,.*|.*,1$|.*,1,.*'

explanation of regexp:

^1,.* --matches 1 at the beginning of a string followed by a `,` and any other char
.*,1$ --matches 1 at the end of a string preceded by a `,` and any other char
.*,1,.* --matches 1 between two `,` which are sourrounded by any other chars
| --is the OR operator

i'm sure this regexp could be much more compact but i'm not that good with regular expressions

obviuosly you can change the category you're looking for in the regular expression (try to replace 1 with 7 on the example above)

Answer 2

WHERE FIND_IN_SET('1', prod_catg)