I am trying to use Full Text Search in SQL Server to search in names. I have two columns indexed for Full Text Search - FirstName and LastName. I want to be able to find matches which are not exact. For example if someone is named "John" I want to find him even if the user searches for "jihn" or "jhn". I also want to search for multiple names and display results matching only one of them but sorted on RANK. I tried using FREETEXTTABLE but it seems like the RANK returned is always 0
SELECT *
FROM FREETEXTTABLE(People, (FirstName, LastName), 'John Smith', 10)
returns people named John Doe, Jane Smith and John Smith but all of them have a RANK of 0 and they are returned in the order they are in the database (by PK or something). I would expect that John Smith is ranked higher than the other names because two terms match while the other two names have one match each. Is there anything else I have to do to make ranking work. Also is there a way to make incorrect spelling work based on how close it is to actual value? Interestingly enough if I replace FREETEXTTABLE with CONTAINSTABLE and put OR between the terms of the query it works and gives some meaningful RANKs. I am trying to work with FREETEXT as I expect (maybe incorrectly) that it will find misspelled or similar names.
