Query about expressibility of the LIKE operator: SQL Server

paddycobbett
paddycobbett used Ask the Experts™
on
I am performing a query based on keywords provided by the user. My table simply has named entries with a "tags" column. I want to return rows whose tags match certain keywords which the user requested. So for example a typical row could look like this:

entry    tags
------     -----
sea       water,salt,sky

If the user requested the keywords 'water' and 'salt' then as it stands the sql looks like:

SELECT entry FROM mytable
WHERE tags LIKE '%water%' OR tags LIKE '%salt%'

.. and so on if we had more keywords to match. Is it possible to compact them into a single LIKE operation?? I'm guessing that the above would check for the 'water' match in tags, then start from scratch and check for a 'salt' match, if i have alot of rows then this may effect peformance? Ideally i'd like some sort of regular expression. I'd imagine it might look like: LIKE '(%water%|%salt%)' ??

Is it possible to do something like the above? If my current way of doing it using multiple LIKEs is the way then please let me know also.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Unfortunately, the only way of acieving the use of a single LIKE compare is to know what order the user will specify the tags in and what order the tags will be in.  Without that information your query is going to have to do multiple LIKE compares . . . and is probably going to run like a dog with 4 broken legs.
However, there is a way to allow the use of a single like comparison.  First, you have to make sure that the tags in the rows are inserted into the filed in alphabetic order.  Using your sample data, the Tags need to be "salt,sky,water".  Then you need to make sure you r user supplied tages are in alphabetic order.  Then you need to modify the user supplied list so that it looks like, again using your example data '%salt%water%'.  The flaw in this search, though, is that it will find the sample data row along with any rows that have "saltwater" in the Tag column.
The better way of handling this is to put the Tags in a separate table with the EntryID being used to join them to "mytable".  THat way, you can join mytable to the Tags table based on the EntryID and use a WHERE clause that includes each of the user entered tags as constraints (WHERE Tags.Tag = 'water' and Tags.Tag = 'salt') or in an IN clause (WHERE Tags.Tag in ('water', 'salt').  Doing that, even though it takes a little more effort on the front end but it will pay dividends as the databse grows.

Author

Commented:
Excuse the delay is responding, you've really helped me think through this one, thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial