SQL Reference ยท JSON and Special Features
SQL full-text search (FTS5)
Reference for SQLite FTS5 full-text search: virtual tables, MATCH queries, bm25 ranking, snippets, plus PostgreSQL, MySQL and SQL Server equivalents.
LIKE '%word%' finds text, but it scans every row, knows nothing about words, and cannot rank results. Full-text search solves this by building an inverted index: a list of every word with the rows that contain it. A query like "salt OR glass" then becomes a quick lookup and the results can be ranked by relevance.
In SQLite this is the FTS5 extension. You create a virtual table with CREATE VIRTUAL TABLE ... USING fts5(...), fill it with text, and query it with MATCH. Every major database has its own full-text feature, with different syntax.
The in-browser runner has no FTS5. The SQLite build used by the live examples on this site was compiled without the FTS5 module (CREATE VIRTUAL TABLE ... USING fts5 fails with "no such module: fts5"). The FTS5 code on this page is therefore read-only; the runnable examples build a small inverted index by hand, which is the idea FTS5 automates.
-- SQLite with FTS5 (read-only here) CREATE VIRTUAL TABLE book_search USING fts5(title, genre); INSERT INTO book_search (rowid, title, genre) SELECT id, title, genre FROM books; SELECT rowid, title FROM book_search WHERE book_search MATCH 'salt OR glass' ORDER BY rank;
Try it
The first example does what FTS5 does internally: it splits each title into words and stores (word, book) pairs in an ordinary table. A search is then a lookup on that table.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
This LIKE approach matches substrings ("the" also matches "Northern" and "Weather" as well as the word "The"), is unindexed and has no real relevance model, which is exactly why FTS exists.
Syntax
CREATE VIRTUAL TABLE name USING fts5(column1, column2, ... [, option = value]); SELECT * FROM name WHERE name MATCH 'query'; SELECT * FROM name('query'); SELECT bm25(name), snippet(name, 0, '<b>', '</b>', '...', 8) FROM name WHERE name MATCH 'query' ORDER BY rank;
| Part | Description |
|---|---|
fts5(col, ...) |
The indexed columns. All content is stored as text. UNINDEXED after a column name stores it without indexing. |
tokenize |
How text is split. Default unicode61 (case-folding, diacritics removed); porter unicode61 adds stemming (running matches run); trigram allows substring search. |
content='table' |
An external content table: FTS5 indexes data held in another table and keeps only the index. |
MATCH 'query' |
The search expression (see below). |
rank |
A hidden column: the bm25() relevance score, where smaller (more negative) is better. Sort with ORDER BY rank. |
snippet(), highlight() |
Return text with matches marked. |
Query syntax: salt road (both words, AND is implicit), salt OR glass, salt NOT road, "small things" (phrase), sal* (prefix), title:salt (column filter), NEAR(salt road, 3).
Dialect differences
| Database | Feature | Example |
|---|---|---|
| SQLite | FTS5 virtual table | WHERE docs MATCH 'salt OR glass' |
| PostgreSQL | tsvector / tsquery, GIN index |
WHERE to_tsvector('english', title) @@ websearch_to_tsquery('english', 'salt or glass') |
| MySQL | FULLTEXT index |
WHERE MATCH(title) AGAINST('salt glass' IN NATURAL LANGUAGE MODE) |
| SQL Server | Full-Text Search catalogs | WHERE CONTAINS(title, 'salt OR glass') or FREETEXT(...) |
| Oracle | Oracle Text | WHERE CONTAINS(title, 'salt OR glass') > 0 |
-- PostgreSQL CREATE INDEX idx_books_fts ON books USING GIN (to_tsvector('english', title)); SELECT title FROM books WHERE to_tsvector('english', title) @@ websearch_to_tsquery('english', 'salt road'); -- MySQL ALTER TABLE books ADD FULLTEXT (title); SELECT title FROM books WHERE MATCH(title) AGAINST('+salt +road' IN BOOLEAN MODE);
Usage notes
- Query syntax is its own language. A stray quote or operator in user input causes a syntax error; wrap user text in double quotes (and double any quotes inside it) or sanitize it.
- Keeping it in sync. A plain FTS5 table is a copy of your data. Update it with triggers on the source table (see trigger patterns) or use an external content table plus the
'rebuild'command. - Relevance.
bm25favors rare words and short documents; weight columns withbm25(name, 10.0, 1.0). - Stop words and stemming are tokenizer choices. Servers like PostgreSQL ship language dictionaries; SQLite's
porteris English only. - Substring search (
%son%) needs thetrigramtokenizer in SQLite orpg_trgmin PostgreSQL. - Not a replacement for a search engine when you need typo tolerance, facets or distributed scale.