SQL Server - Fulltext Weights/Ranking based on matched Column

How can I apply a higher rank to a search result if a search word was found in a spec开发者_StackOverflowific column?

For example, if the search term is "King", and it was found in "LastName", I want that to be ranked higher than if it appears as part of "King Street" in the address.

How do I do that?

Check 2008 Books Online CONTAINSTABLE.

You may be able to use the column_name / column_list parms in combination with the weight_value to achieve your desired results.

Use column_name to apply more weight to LastName, and column_list for the others.

You can try multiple full-text queries with a common table expression and UNIONs to stitch things together:

-- define some CTEs
WITH
    -- full-text query on LastName
    FT_LastName([Key], [Rank]) AS (
        SELECT [Key], [Rank] FROM CONTAINSTABLE(Foo, (LastName), 'options', 100)
    ),
    -- full-text query on Address
    FT_Address([Key], [Rank]) AS (
        SELECT [Key], [Rank] FROM CONTAINSTABLE(Foo, (Address), 'options', 100)
    ),
    -- combine
    FT_ReweightedTemp([Key], [RankLastName], [RankAddress]) AS (
        SELECT [Key], [Rank], 0 FROM FT_LastName
        UNION ALL
        SELECT [Key], 0, [Rank] FROM FT_Address
    ),
    -- calculated weighted ranks
    FT_Reweighted([Key], [Rank]) AS (
       SELECT [Key], MAX([RankLastName]) * 2 + MAX([RankAddress]) * 1
       FROM FT_ReweightedTemp
       GROUP BY [Key]
    )    

-- carry on
SELECT ...
FROM FT_Reweighted
INNER JOIN ...
ORDER BY [Rank]

In this example I weight LastName over Address by factor of 2:1. The use of the addition operator also makes rows with results in both columns twice the weight of those results coming from only one column. You might desire to use some kind of normalization and averaging to combine the weighted ranks instead.