I have a search that uses fulltext searching in SQL Server 2005 to do exact matches first, then does partial matches, etc... However, the way I'm doing it seems overkill...
I basically need to do an exact match against a Title, keyword & a whole load of other fields (I've not put them in the examples below), but the order for search results returned is important, e.g. title matches before keyword matches.
I'm doing the searches against each field and returning the results to a temporary table where I then remove any duplicates.
EXACT MATCH
SELECT ID, title, keywords FROM Products WHERE CONTAINS((Title), '"harry" AND "potter"')
SELECT ID, title, keywords FROM Products WHERE CONTAINS((keywords), '"harry" AND "potter"')
EXACT MATCH WITH WILDCARD
SELECT ID, title, keywords FROM Products WHERE CONTAINS((Title), '"harry" AND "potter*"')
SELECT ID, title, keywords FROM Products WHERE CONTAINS((keywords), '"harry" AND "potter*"')
KEYWORD SEARCH
SELECT ID, title, keywords FROM Products WHERE CONTAINS((Title), '"harry" OR "potter"')
SELECT ID, title, keywords FROM Products WHERE CONTAINS((keywords), '"harry" OR "potter"')
KEYWORD WITH WILDCARD
SELECT ID, title, keywords FROM Products WHERE CONTAINS((Title), '"harry*" OR "potter*"')
SELECT ID, title, keywords FROM Products WHERE CONTAINS((keywords), '"harry*" OR "potter*"')
Is there a better way of doing this?