Scenario
I've stored some keywords about each product in a column with name Tags .When a user searches a word that exists in this column, a relevant product must be shown. The user may enter some general words in the search query(for example: an,or,some), so I currently assign a weight to each word based on generality.
Using
Tags column is full text indexed and I'm using Containstable for searching keywords.
Problem
Now after few months the table size has increased significantly and I am seeing a problem using Containstable. When user searches for a word(number of occurrence of that word in all Tags column of relevant rows is the same), Rank of result rows is not equal and each row that has smaller keyword count(small from a threshold), is getting a higher Rank.
This is not problem and based on https://technet.microsoft.com/en-us/library/ms142524%28v=sql.105%29.aspx , ContainsTable use IndexedRowCount and KeyRowCount for Ranking.
Now is there a way to Rank each row just based on weighted sum of the word that occurrs in Tags column?
Update
I need something like Contains function plus weight.
based on https://msdn.microsoft.com/en-us/library/ms187787.aspx , weighted_term not affect in Contains.
My new code without ContainsTable in below. this code is very slow!
declare @q nvarchar(100)='word1#0.5,word2#0.4'
declare @wordsTable table(word nvarchar(30),weight decimal)
insert into @wordsTable
select substring(items,0,CHARINDEX('#',items)) as word,substring(items,CHARINDEX('#',items)+1,LEN(items)) as weight from split(@q,',')
declare @counter int
select @counter=COUNT(*) from @wordsTable
_____________________________________________
select Tags,SUM(rank) as ranks
from(
select (0.5) as rank, Tags from Product where contains(Tags,@word1)
union
select (0.4) as rank, Tags from Product where contains(Tags,@word2))
group by Tags