MySQL using LIKE command to find between semi-colon

Question

This is my data

385_white   99  swarovski   12  black;blue
386_white   99  swarovski   12  black;blue;green
387_white   99  swarovski   12  yellow;green
389_white   99  swarovski   12  white;silver
385_white   99  swarovski   12  silver

This is an example query

SELECT *  FROM `products` WHERE `category` LIKE 'swarovski' AND `colours` LIKE 'silver'

The results returned are empty. If the user was to have black, then it should return both the first records. There is no way for me to edit the color;color;color format. That is fee sable at this time.

Answer 1

The penalty of a denormalized schema like this is you have to do more work with your query to ensure an accurate match:

SELECT *  
FROM `products` 
WHERE `category` = 'swarovski' 
AND (
    `colours` = 'silver' 
    OR `colours` LIKE 'silver;%' 
    OR `colours` LIKE '%;silver;%' 
    OR `colours` LIKE '%;silver' 

)

SQL Fiddle Example

Answer 2

I prefer doing a single comparison:

SELECT *  
FROM `products` 
WHERE `category` = 'swarovski' and
      concat(';', `colours`, ';') like ';silver;' 

That is, add the delimiters to the beginning and end of the column and then do a single like.