SQLite LIKE & ORDER BY Match query
sql, sqlite
Solution
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 ;
Problem
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: - If the field is equal (e.g. "John") - If the field starts with "John" (e.g. "John Doe") - 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?