I have received a spec to add a relevance score to search results, based on which column the result is in. As an example I have a product table with, amongst other fields, keywords,productNames and brands.
I currently check to find a product by link to
JOIN CONTAINSTABLE(Products, (keywords, productNames, brands), '"NIKE*"')
Now this will find the record with the search term on but I need to weight the results by column eg. keywords scores 1, productNames scores 2, brands 4, etc. The sum of the scores I can then add together to give my relevancy of result. i.e. if "Nike" is in all three columns it would score 7, just in brands 4, etc.
To facilitate this I need to know which columns containstable matches on, but haven't found any details on that.
I've looked at the ISABOUT option, but that's for weighting multiple search terms in a single column.
At the moment I have a case statement
CASE WHEN CONTAINS (Keywords, '"Nike*"') THEN 1 ELSE 0 END +
CASE WHEN CONTAINS (productNames, '"Nike*"') THEN 2 ELSE 0 END +
CASE WHEN CONTAINS (brands, '"Nike*"') THEN 4 ELSE 0
AS Relevance
Which does work, but seems to be very wasteful since containstable must already be doing the work.
If anyone has any ideas then they'll be gratefully received.