Okay so this was dumb of me. The weights work correctly. The issue I was having was because of the forced UTF8 encoding (I simply set "utf8" on the db, didn't rebuild. I set it back to latin1 and it works now).
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
Best Answer
The "Full" in Full Text Search means match full words, not parts of words
You should use RegExp or LIKE or tri-gram matching instead
You can mimic suffix matching by reversing words in your index and also reversing your queries, but this takes more space: