Use Ms SQL SERVER full text search to match words

Question

I need to write a query in sql using full text that returns records in the order of matching words count

exmaple: in data base

row 1 = "brown cow" //1 match
row 2 = "lazy dog" //2 matches

User input: "The quick brown fox jumps over the lazy dog"

both inputs would be return with row 2 on the top

Answer 1

Google SQL Server Full Text Search and you will find many resources.

Here is a nice article to get you started.

Answer 2

I don't see anything that natively does this. You might have to create a function which compares the results to your search string in order to determine how many matches exist.

You could then pass the results of the free text search into that function which would return a match count. Finally, you'd order the results by the match count.

Answer 3

In MS SQL, for a guerilla approach you can do something like this:

select * from MATCHTABLE where patindex('%'+MATCHCOLUMN+'%','The quick brown fox jumps over the lazy dog')>0

where MATCHTABLE is the table with your two example rows, and MATCHCOLUMN is the column name with the text.

However, performance with this approach will not be anything like fulltext searching.