Searching for separate words in a sentence in SQL query?

Question

If I have a database entry of "Mens White Crew T-Shirt", what SQL query do I need to run to return the entry if a user searches "Mens T-Shirt" or "White T-Shirt"?

I am using Microsoft SQL server 9:

When using:

WHERE "Mens T-Shirt" like '%' + @SearchTerm + '%'

it does not bring back "Mens White Crew T-Shirt"?

Thanks everyone!

if your DBMS supports arrays and regular expressions you could split up each title and search term into all words and then match the arrays. But a full text search is the best way to go. Relational DBMS are not meant for this kind of query. — , Apr 09 '13 at 17:06
Can you rework your DB structure to parse out a separate list of keywords for each item? — mbeckish, Apr 09 '13 at 17:07

Tim Lehner · Answer 1 · 2013-04-09T20:03:38.277

If I have a database entry of "Mens White Crew T-Shirt", what SQL query do I need to run to return the entry if a user searches "Mens T-Shirt" or "White T-Shirt"?

select *
from Products
where Description like '%' + replace(@SearchTerm, ' ', '%') + '%'
-- where 'Mens White Crew T-Shirt' like '%Mens%T-Shirt%' -- Returns true
-- where 'Mens White Crew T-Shirt' like '%White%T-Shirt%' -- Returns true

You can get as creative as you wish with the replacements (other characters such as that hyphen, for example), but this is somewhat of a hack. It doesn't exactly work for "Men's" or "Shirt White"

Really, this type of query is what Full-Text Search is all about, and I second a_horse_with_no_name's recommendation to use that.

If that is not possible, you will probably have to go down the path of sanitizing and splitting the search term and the searched columns, and dynamically creating your query. You might end up with something that eventually looks like this (using parameters, hopefully) coming from your app:

exec sp_executesql N'
        select *
        from Products
        where Description like ''%'' + @SearchTerm1 + ''%''
            and Description like ''%'' + @SearchTerm2 + ''%''
            and Description like ''%'' + @SearchTerm3 + ''%''
            -- etc.',
    N'@SearchTerm1 varchar(100), @SearchTerm2 varchar(100), @SearchTerm3 varchar(100)',
    'Mens', -- What about "men" or "man", and this would still get "womens"
    'T', -- Any "T", yikes...possibly have to sanitize the column and search term to "tshirt"
    'Shirt'

But this still wouldn't cover everything that FTS can, and possibly not as fast.

I just wasted 5 minutes between your original and your edit. I guess my link to the split function is still a little useful. hehe. — TTT, Apr 09 '13 at 19:55
Ha, nice. I just wanted to give a more concrete example of a dynamic version, but FTS is probably the way to go. — Tim Lehner, Apr 09 '13 at 19:59

score 1 · Answer 2 · edited May 23 '17 at 11:56

You could provide the user an option when they do the search, for "Contains exact phrase", or "Contains all words". The exact phrase is what you are doing now. To do a match for all words, you need to first split the search term into words, like this:

T-SQL Split String

Just make sure you change the comma to a space when you use the function. Then you can add a WHERE clause to your search for each term that comes back from your split function:

WHERE Item LIKE '%Mens%'
AND Item LIKE '%T-Shirt%'

Note, this is similar to Tim's answer, except that in his example the words had to be in the correct order, whereas here they do not. (Actually- his edit has this idea too.)

But to reiterate, check out the Full Text Search since if you can implement that you'll probably be happier than what you can do this way.

Searching for separate words in a sentence in SQL query?

2 Answers2