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.

This would be much easier if you did not store a comma-separated list of tags. You can easily store these in separate rows and then concatenate them together to display, etc. I would **strongly** suggest that you look at normalizing your database. — Taryn, Sep 03 '13 at 22:50
The tags are only one column out of about 30 that describe a particular object in the database. Creating a new row for every tag would be monstrous as I have nearly 100k rows of items in the database. Also, this is the format that the information was handed to me. — Nicholas Cardot, Sep 03 '13 at 22:52
@NicholasCardot: You wouldn't create an entirely new row for that table. You'd create a new row in a separate table which has a foreign key back to this table. Then you'd join those tables when querying the data. Databases are really good at relational data. — David, Sep 03 '13 at 22:53
That is actually sort of what I am working on. I don't know exactly how to use the foreign key that you mentioned, but I'm building a list in a separate table of primary categories and I'm working to bring all these hundreds of tags in the current table to simply be displayed under the 20 or 30 primary tags / categories. Each time I identify a new tag, I have to run a search through the current system to find all the ones that match and then I'm using the other table for displaying tags. — Nicholas Cardot, Sep 03 '13 at 22:57
@NicholasCardot As David stated you would create a separate table to store the tags, and then create a join table to associate your main table to the tags --- see a previous answer of mine to give you a general idea on the design -- http://stackoverflow.com/a/18520723/426671 — Taryn, Sep 03 '13 at 22:58
@Nicholas Cardot: http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php — zerkms, Sep 03 '13 at 22:58

Bohemian · Answer 1 · 2014-03-27T11:55:08.390

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.

edited Mar 27 '14 at 11:55

answered Sep 03 '13 at 23:04

Bohemian

412,405
93
575
722

score 1 · Answer 2 · answered Sep 03 '13 at 23:09

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);

score 1 · Answer 3 · answered Sep 04 '13 at 04:15

1

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

SAMPLE FIDDLE

answered Sep 04 '13 at 04:15

Praveen Prasannan

7,093
10
50
70

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

3 Answers3