Sql-server – SQL Full Text Search – Result Order changing

full-text-searchranksql server 2014sql-server-2012

I am using Full Search text to implement search functionality. Basically I will have to do search on all the columns with the search string. Search string will have combination of values from different columns. However I will not know what all columns will I have to search. For example, using the following query in full text SQL search to find customer based on Last Name, Address and Zip

DECLARE @SearchString VARCHAR(200)
SET @SearchString = 'Adam OR West OR 60608'

SELECT Id, First_Name, Last_Name, Address,City,State,Zip, Rank
FROM Patient_Ex PAT 
    INNER JOIN CONTAINSTABLE(Patient_Ex,*, @SearchString) CT 
        ON PAT.Id = CT.[Key]

Results of the above query

Id  FirstName   LastName    Address City    State   Zip RankID
25  Adam Avenue Adam     North Albay Avenue Chicago IL  60619   272
26  Adam Avenue West    South Escaaba Avenue    Chicago IL  60620   272
27  Adam Avenue Adam     eclaire Avenue Chicago IL  60634   272
28  Adam Avenue West    Leamington Avenue   Chicago IL  60657   272
29  Adam Avenue Adam    North England Avenue    Chicago IL  60660   272
30  Adam Avenue West    West 59th Street    Chicago IL  60608   272
31  Adam Avenue Adam    West 21st Place Chicago IL  60608   272
32  Adam Avenue West    South Albay Avenue  Chicago IL  60655   272

However, I am expecting the result to be something like this below. If the same row has all the three search criteria matching I would like that to be displayed at the top. In this scenario, a record having Last name as Adam, Address containing West and Zip 60608 should be displayed as top records

Id  FirstName   LastName    Address City    State   Zip RankID
30  Adam Avenue Adam    West 59th Street    Chicago IL  60608   272
31  Adam Avenue West    West 21st Place Chicago IL  60608   272
27  Adam Avenue Adam    eclaire Avenue  Chicago IL  60634   272
28  Adam Avenue West    Leamington Avenue   Chicago IL  60657   272
29  Adam Avenue Adam    North England Avenue    Chicago IL  60660   272
25  Adam Avenue West    North Albany Avenue Chicago IL  60619   272
26  Adam Avenue Adam    South Escanaba Avenue   Chicago IL  60620   272
32  Adam Avenue West    South California Avenue Chicago IL  60655   272

How do I modify the query to get the result similar to the above one.
I searched the forum and found a similar question, however there is no answer for it. Any help to resolve the issue will be of great help.

Thanks in advance,

Santha

Best Answer

Try This:

DECLARE @SearchString VARCHAR(200)
SET @SearchString = 'Adam OR West OR 60608'

SELECT Id, First_Name, Last_Name, Address,City,State,Zip, CT.Rank
FROM Patient_Ex PAT 
    INNER JOIN CONTAINSTABLE(Patient_Ex,*, @SearchString) CT 
        ON PAT.Id = CT.[Key]
ORDER BY CT.RANK DESC

Explanation: The table produced by CONTAINSTABLE includes a column named RANK. The RANK column is a value (from 0 through 1000) for each row indicating how well a row matched the selection criteria.

Update:

Try adding weights to your keyword and see if it changes things:

DECLARE @SearchString VARCHAR(200)
SET @SearchString ='ISABOUT (Adam WEIGHT (.8), West WEIGHT (.4), 60608 WEIGHT (.2) )'
SELECT Id, First_Name, Last_Name, Address,City,State,Zip, CT .RANK  
FROM Patient_Ex PAT 
INNER JOIN CONTAINSTABLE(Patient_Ex,*,@SearchString,LANGUAGE N'English' ) AS CT  
ON PAT.Id = CT .[KEY]  
ORDER BY CT.RANK DESC;  
GO

If the above doesn't works either try rebuilding your Catalog if you have very few rows in database as people have reported wrong rank outputs with databases having less no of rows while the ranking working fine with database having large no. of rows,another workaround is to create dummy table in your database and index it in your catalog. Please read this if you want to learn more about how ranks are calculated. Source : 1,2

Related Question