Building a Weighted Search Engine with Pure SQL

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:

1. Exact Match: The best.
2. Starts With: Very good.
3. Contains: Okay.

A Real Business Scenario: Search That Feels Better Without a New Service

Many internal tools, catalogs, and admin dashboards do not need a dedicated search stack on day one. They need a search result order that feels reasonable to humans.

Typical examples:

• a product picker where exact names should beat partial matches
• a help-center lookup where title matches should outrank description matches
• an internal admin tool where "Pro Max" should beat "Programming Book" for the query pro

That is where weighted SQL search works well. It gives you control over ranking without introducing a separate indexing system too early.

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

A Common Mistake: Leading Wildcards Everywhere

The easiest search pattern to write is also often the hardest to scale:

WHERE LOWER(name) LIKE LOWER('%query%')

The leading % means "match anywhere", which is flexible but often prevents ordinary prefix indexing from helping. That can be fine for small datasets. It becomes a problem when the table grows and every request scans a large portion of the column.

If your product can tolerate prefix matching for the first pass, query% is often much cheaper than %query%.

Boundary and Performance Notes

Weighted LIKE search is a good middle ground, but it has limits.

• It works best on small to medium tables or on filtered subsets where full scans are still acceptable.
• Ranking logic becomes harder to maintain as soon as you add many fields, language rules, typo tolerance, or stemming.
• Case normalization with LOWER() can reduce index friendliness unless you use database features such as functional indexes or normalized search columns.
• Search quality and search speed are separate problems. A query can rank results reasonably but still be too expensive on a hot path.

The right question is not just "Can SQL do search?" but "At what scale and quality level does this still remain the simplest good solution?"

When NOT to Use This Pattern

Move beyond weighted LIKE when:

• you need typo tolerance, stemming, or language-aware ranking
• relevance depends on many text fields with competing weights
• result latency must stay low on large datasets under real user traffic

At that point, full-text search, trigram search, or a dedicated search service may be a better fit than continuing to pile more CASE branches onto one query.

Official References

• SQLite query planner overview for how pattern matching and indexing interact in SQLite.
• PostgreSQL pattern matching documentation for LIKE, ILIKE, and regex alternatives in a major SQL engine.
• SQLite FTS5 documentation for the next step once weighted LIKE search stops being sufficient.

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.

Related Articles

• Using Regular Expressions in SQL: Pattern Matching Deep Dive for more expressive text-matching patterns before or alongside LIKE-based ranking.
• SQLite Full-Text Search FTS5 Explained for the next step when basic contains matching starts to hit scalability limits.
• SQL CASE Statements Explained for the branching logic that powers weighted search scoring.