Search multiple words and intelligent sorting in sqlite

Question

I have an app where a user can type a search query that will get a result from an SQLite database. If a user type word1 word2 word3, I use the following query :

SELECT * FROM myTable 
WHERE myColumn LIKE word1 
   OR myColumn LIKE word2 
   OR myColumn LIKE word3 
ORDER BY myColumn

I get all values that have at least one of the three words typed in. I would like to get the results that match the three values first, then those who match two values and then those who match only one value

Answer 1

You can sum the boolean expressions in your WHERE clause and order descending:

SELECT * 
FROM myTable 
WHERE myColumn LIKE 'word1'  OR myColumn LIKE 'word2' OR myColumn LIKE 'word3' 
ORDER BY ((myColumn LIKE 'word1') + (myColumn LIKE 'word2') + (myColumn LIKE 'word3')) DESC

Note that if you want exact match of the words you should use = instead of LIKE, but if you want partial match you must concatenate the % wildcard:

myColumn LIKE '%' || 'word1' || '%'

or:

myColumn LIKE '%word1%'