SQLite LIKE & ORDER BY Match query

Question

I need a SQLite query that searches 1 field only using LIKE.

Basic example:

SELECT name FROM table WHERE name LIKE "%John%" ORDER BY name LIMIT 10;

The problem is that I want the result to be ordered in this way:

1. If the field is equal (e.g. "John")
2. If the field starts with "John" (e.g. "John Doe")
3. If the field contains "John" (e.g. "Jane John Doe")

The following query achieves the expected result, but is slow:

SELECT name FROM table WHERE name LIKE "%John%" ORDER BY CASE WHEN name = "John" 
THEN 1 ELSE 2 END, CASE WHEN name LIKE "John%" THEN 1 ELSE 2 END, name LIMIT 10;

The query above is slower (or I tested it incorrectly) than the alternative of using 3 separate queries (one for exact match, one for starts with and one for contains).

Are there any other alternatives?

Answer 1

Try in this way :

SELECT name 
FROM table 
WHERE name LIKE "%John%" 
ORDER BY (CASE WHEN name = "John" THEN 1 WHEN name LIKE "John%" THEN 2 ELSE 3 END),name LIMIT 10 ;

Answer 2

It should suffice to order on your equivalence tests:

ORDER BY name = "John" DESC, name LIKE "John%" DESC

ORDER BY clauses are evaluated from left to right.