Sql Server searching for multiple words and ranking results

Question

Suppose a user enters “Vintage Audi Car” as search criteria

My table has a Description field as Varchar (Max)

I want to find user entered values ​​like Vintage OR Audi OR Car. The result should display lines containing all three words in the Description above, and lines with any two words, and then lines with any one word.

Let me know how this can be achieved.

I am open to using full-text search.

Answer 1

This should be possible with a full-text search with the CONTAINSTABLE command:

SELECT * FROM Car INNER JOIN CONTAINSTABLE(Car, Description, 'ISABOUT (Vintage weight (.5), Audi weight (0.5), Car weight (0.5) )') AS A ON Car.Id = A.[KEY] ORDER BY A.[RANK] DESC; 

The rank is calculated by weight, so the row with the description "Vintage Audi" will get a higher rank than the row with the description "Audi".

Answer 2

Even I had the same problem, I solved it with the MYISAM engine

change car table

 ALTER TABLE Car ENGINE = MYISAM; 

then

 select * FROM Car WHERE MATCH (Description) AGAINST ('Vintage Audi Car' IN BOOLEAN MODE);