SQL multi-word search sorted by number of matches

Question

I am trying to compose an SQL SELECT query with a few search words. But I want the result to be ordered by the number of word matches.

For example, let the search string be "red green blue." I want the results to contain all these three words on top, after which the results contain two of them, and in the end - only one word.

SELECT
    *
FROM
    table
WHERE
    (col LIKE '%red%') OR
    (col LIKE '%green%') OR
    (col LIKE '%blue%')
ORDER BY
    ?????

Thanks in advance!

Answer 1

ORDER BY
(
CASE 
WHEN  col LIKE '%red%' THEN 1
ELSE 0
END CASE
+     
CASE 
WHEN  col LIKE '%green%' THEN 1
ELSE 0 
END CASE
+    
CASE 
WHEN  col LIKE '%blue%' THEN 1
ELSE 0
END CASE
)  DESC

If your provider has a database IF, you can use it instead CASE(for example, for Mysql you can write   IF (col LIKE '%red% , 1,0) + IF(....'

Answer 2

? SQL Server, , .

http://msdn.microsoft.com/en-us/library/ms142583.aspx

Answer 3

If you use MySQL: SQL Server Freetext match - how to sort by relevance