0

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

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
2
  • Thanks for responding. If you see the results in my query every row as the same rank, so order by Rank will not help my scenario. Commented Jan 5, 2017 at 11:10
  • Please check my updated answer. Commented Jan 5, 2017 at 18:54

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.