I originally posted this on allthingswilson.com almost 2 years ago. However, I find it to be very helpful so I am reposting it here. I realize that I am not blazing any new trails here, but hopefully someone will find this useful.
A client of our’s recently approached us with a simple request, to create a business search which would search keywords on the business’ name and description in our SQL Server database. This was simple enough to achieve, by creating a FULLTEXT index on the table with the appropriate fields ( title, description ) and executing the following query:
SELECT Members.*, SEARCH_TBL.RANK AS rank FROM Members INNER JOIN FREETEXTTABLE(Members, *, ‘breakfast’) SEARCH_TBL ON Members.memberID = SEARCH_TBL.[KEY] ORDER BY rank DESC
Which yields the following results for the keyword ‘breakfast’:
|23||Holiday Inn – Downtown||<Long Text>||276|
|2||Days Inn – North||<Long Text>||155|
|43||Best Breakfast Buffet in Town||<Long Text>||146|
As we can see, these results are unsatisfactory as two businesses appear above ones that should be first. This is due to the fact that these hotels keyword-loaded their description by boasting about their, no doubt, wonderful continental breakfast. Well, we have been there and we have been to the BBBIT and IHOP, and we realize that it is not even a contest as to which one is better. So we would like for the genuine greasy spoon breakfast joints to appear ‘ranked’ above the giant monster mega hotel chains.
Unfortunately, SQL Server has no native way of ‘prioritizing’ columns in a FULLTEXT search clause, that I know of (unlike Karl Hungus, I am not an expert). However, this needed to be done, and I did not want to go messing with the code, so I set about trying to come up with a way to do it in my stored procedure. I suck at T-SQL, but I am pretty good at joining tables, it’s simpler for my simple mind.
Here is the new query:
SELECT Members.*, NAME_SRCH.RANK AS rank1, SRCH.RANK AS rank2 FROM Members LEFT OUTER JOIN FREETEXTTABLE(Members, name, ‘breakfast’) NAME_SRCH ON Members.memberID = NAME_SRCH.[KEY] LEFT OUTER JOIN FREETEXTTABLE(Members, *, ‘breakfast’) SRCH ON Members.memberID = SRCH.[KEY] ORDER BY rank1 DESC, rank2 DESC
This query yielded the following result set:
|43||Best Breakfast Buffet in Town||<Long Text>||265||146|
|2||Cozy Bed & Breakfast||<Long Text>||265||120|
|23||Holiday Inn – Downtown||<Long Text>||<NULL>||276|
Now you see we have much better results, the Holiday Inn hung in there but we also introduced a new business, Cozy Bed & Breakfast, which I think we can agree is a desirable result for a breakfast search. IHOP still came in after the hotel, because they do not have breakfast in their name. But alas, as we all know by now, outside of Google, no search is perfect.