CODEWITHSUNDEEP
sql-serverfull-text-search
David
David

Reputation: 141

Giving precedence/weight to a column using FREETEXTTABLE

I am using SQL Server Full Text Search, with the keyword FREETEXTTABLE to return a table of results based on a few columns, searching for a keyword.

Now I have 2 main columns that I am searching on, Title and Description, I want to give precedence to the Title column as this will most likely have my results but Description might contain results too but I want it to give precedence for a word in Title over Description (but I don't want to use CONTAINSTABLE as this is too specific). Is there a way to give columns weights/precedence using FREETEXTTABLE?

Upvotes: 14

Views: 3531

Answers (2)

Aba
Aba

Reputation: 614

I used a calculated field to "double" the title:

ALTER TABLE tableName ADD TitleX2 AS (Title + ' ' + Title)

select Rank as rnk, [key] from freetexttable(tableName,(TitleX2, Description), 'free text string')

Upvotes: 0

boydc7
boydc7

Reputation: 4643

You'll need to use 2 queries with a union, providing a 'weight' of your own, something like this:

select  [key], sum(rnk) as weightRank
from
        (
            select Rank * 2.0 as rnk, [key] from freetexttable(tableName,Title,'free text string')
            union all
            select Rank * 1.0 as rnk, [key] from freetexttable(tableName,Description,'free text string')
        ) as t
group by [key]

Upvotes: 16

Related Questions