Recently, while looking at my site stats, I noticed a disturbing trend, people are still visiting my site (not really). So, it is with that in mind that I embark on this next post, which is sure to kill off the last vestigious of any readership that I may have had. 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:

   1: SELECT Members.*, SEARCH_TBL.RANK AS rank

   2: FROM   Members INNER JOIN

   3:        FREETEXTTABLE(Members, *, ‘breakfast’) SEARCH_TBL

   4:            ON Members.memberID = SEARCH_TBL.[KEY]

   5: ORDER BY rank DESC

Which yields the following results for the keyword ‘breakfast’:

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:

   1: SELECT Members.*,

   2:        NAME_SRCH.RANK AS rank1, SRCH.RANK AS rank2

   3: FROM   Members LEFT OUTER JOIN

   4:   FREETEXTTABLE(Members, name, ‘breakfast’) NAME_SRCH ON

   5:     Members.memberID = NAME_SRCH.[KEY] LEFT OUTER JOIN

   6:   FREETEXTTABLE(Members, *, ‘breakfast’) SRCH ON

   7:     Members.memberID = SRCH.[KEY]

   8: ORDER BY rank1 DESC, rank2 DESC

This query yielded the following result set:

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.

This entry was posted on Tuesday, October 9th, 2007 at 3:57 pm and is filed under programming tips. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.