CODEWITHSUNDEEP
sqlsql-server-2005words
dallardtech
dallardtech

Reputation: 185

Searching for separate words in a sentence in SQL query?

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!

Upvotes: 0

Views: 3430

Answers (2)

TTT
TTT

Reputation: 28944

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.

Upvotes: 1

Tim Lehner
Tim Lehner

Reputation: 15251

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.

Upvotes: 3

Related Questions