SQL Containstable Multiple columns - Which Column Contains Result

558 Views Asked by At

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.

0

There are 0 best solutions below