SQL multiple words search, ordered by number of matches

I'm trying to compose an SQL SELECT query with multiple search words. But I want the result be ordered by number of words matches.

For example, let the search string is "red green blue". I w开发者_JAVA百科ant the results which contains all these three words on top, after that the results, which contains two of them, and at the end - only one word matches.

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

Thanks in advance!

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 DB vendor has IF, you can use it instead of CASE (e.g., for Mysql you can write IF (col LIKE '%red% , 1,0) + IF(....'

What platform are you using? if SQL Server, then it sounds like a Full Text Search archtecture would be your best fit.

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