Search code examples
sqlsearch-multiple-words

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 want 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!

Solution 
  • 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(....'