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,