How do I Use Wildcard (%) to Search but not include spaces?

Question

I have a list of comma separated tags in a column in my database. I was using a simple wildcard to pull items that matches a tag that I was searching for. However, I have discovered that some tags are included as part of a title for other tags and that they are being returned also. For example, searching for hunting these rows might be returned:

hunting, fishing, outdoors <-- Should be returned
sporting goods, hunting    <-- Should be returned
bargain hunting, sale      <-- Should NOT be returned
bullets, guns, hunting     <-- Should be returned

Currently I am using:

WHERE column LIKE '%hunting%'

What should I do to make this work more appropriately with these comma seperated lists. Also, please bear in mind that some rows may have only one tag and hence have no commas at all. Thanks.

Answer 1

Use RLIKE with the appropriate regex:

WHERE column RLIKE '(^|, )hunting(,|$)'

See an SQLFiddle live demo of this condition working correctly with the sample input from the question.

Answer 2

Without using regular expressions:

WHERE column LIKE '%, hunting' 
   OR column LIKE '%, hunting,%'
   OR column LIKE 'hunting, %'

See a demo

The solution here is to normalize your table.

CREATE TABLE products
    (`id` int, `name` varchar(4));

INSERT INTO products
    (`id`, `name`)
VALUES
    (1, 'gun'), 
    (2, 'fish');

CREATE TABLE keywords
    (`id` int, `keyword` varchar(15));

INSERT INTO keywords
    (`id`, `keyword`)
VALUES
    (1, 'hunting'),
    (2, 'fishing'),
    (3, 'outdoors'),
    (4, 'sporting goods'),
    (5, 'bargain hunting'),
    (6, 'sale'),
    (7, 'bullets'),
    (8, 'guns');


CREATE TABLE productKeywords
    (`productId` int, `keywordId` int);

INSERT INTO productKeywords
    (`productId`, `keywordId`)
VALUES
    (1, 1),
    (1, 3),
    (1, 4),
    (2, 2),
    (2, 5);

Answer 3

select * from Table1
where find_in_set('hunting',`text`) 
or find_in_set(' hunting',`text`)

SAMPLE FIDDLE