Ranking and highlighting matches in SQL

A search result needs an order and a preview, and FTS5 has both built in.

SELECT
    title,
    ROUND(BM25(doc_search, 10.0, 1.0), 3) AS score,
    SNIPPET(doc_search, 1, '[', ']', '...', 8) AS preview,
    HIGHLIGHT(doc_search, 0, '<b>', '</b>') AS marked_title
FROM doc_search
WHERE doc_search MATCH 'plan*'
ORDER BY rank
LIMIT 10;

How it works

1. ORDER BY rank sorts by bm25 relevance; more negative is better.
2. bm25() takes per-column weights, so a title hit can outrank a body hit.
3. snippet() returns the matching fragment with your own markers around it.

Keywords and builtins used here

• AS
• BY
• FROM
• LIMIT
• MATCH
• ORDER
• SELECT
• WHERE

The run, in numbers

Lines

9

Characters to type

243

Tokens

64

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 40 seconds.

Type this snippet

Step 3 of 4 in Full-text search, step 11 of 14 in JSON & search.

← Previous Next →