When adding search to an app, the temptation is to jump straight to heavy tools like Elasticsearch or Algolia. But for many projects, your database is already a powerful search engine.
The problem with a simple WHERE name LIKE '%query%' is that it's "dumb". It returns everything that matches, in no particular order. A user searching for "Apple" wants "Apple Store" to appear before "Pineapple".
In this guide, we'll build a "Smart" search engine that ranks results by relevance:
- Exact Match: The best.
- Starts With: Very good.
- Contains: Okay.
The Secret Sauce: CASE Statements
We can use a CASE statement in our ORDER BY clause to assign a "score" to each match type.
SELECT name,
CASE
WHEN name LIKE 'query' THEN 1 -- Exact match
WHEN name LIKE 'query%' THEN 2 -- Starts with
WHEN name LIKE '%query%' THEN 3 -- Contains
ELSE 4 -- No match
END as relevance
FROM products
WHERE name LIKE '%query%'
ORDER BY relevance ASC;
Interactive Example: Searching for "Pro"
Let's search a product catalog for the term "Pro". We expect "Pro Max" (Starts with) to appear before "GoPro" (Contains).
Improving the Search: Case Insensitivity
By default, LIKE is case-insensitive in SQLite for ASCII characters, but it's good practice to be explicit, especially if moving to PostgreSQL later (where ILIKE is needed).
In SQLite, you can use the LOWER() function to normalize both sides:
WHERE LOWER(name) LIKE LOWER('%query%')
Multiple Keywords
If you need to search multiple columns (e.g., Title OR Description), simply add them to your CASE logic with different weights.
ORDER BY
CASE WHEN title LIKE '%query%' THEN 1 ELSE 10 END +
CASE WHEN description LIKE '%query%' THEN 2 ELSE 10 END
ASC
Conclusion
You don't need complex infrastructure to build a good search experience. By using CASE statements to weight your results, you can give users highly relevant results using nothing but standard SQL.
This approach scales well for datasets up to roughly 50,000 - 100,000 rows, which covers 95% of small to medium applications.