Hands on Full-text Search in SQLite3

Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

FTS5 (Full-text Search) in SQLite3¶

If tokens in the search phrase are separated by either spaces or plus signs, then they are matched in order by tokens in the text. To match tokens orderless, you can separate tokens in the search phrase with the keyword AND.

%defaultDatasource jdbc:sqlite:fts.sqlite3

CREATE VIRTUAL TABLE posts 
USING fts5(title, category, tags, content)
;

INSERT INTO posts (
    title,
    category,
    tags,
    content
) VALUES(
    'Use JupyterLab Notebooks in Pelican',
    'Life',
    'life, blog',
    'Introduction on how to use JupyterLab notebooks in Pelican to make blogging easier.'
), (
    'ODBC vs JDBC in Python',
    'Programming',
    'programming, Python, JDBC, ODBC',
    'Overall speaking, Python has better ODBC support than jDBC support. Most database related packages in Python support or rely on ODBC.'
), (
    'Tips for JavaScript',
    'Programming',
    'programming, JavaScript, JS, forntend',
    'Node.js is a javaScript runtime environment outside browsers.'
)
;

select * from posts

Match¶

SELECT * FROM posts WHERE posts MATCH 'Python'

SELECT * FROM posts WHERE posts MATCH 'programming'

The Equal Sign¶

The equal sign (=) is equivalent to the match keyword.

SELECT * FROM posts WHERE posts = 'programming'

The Table-valued function Syntax¶

SELECT * FROM posts('programming')

Order by the Default Rank¶

SELECT * FROM posts WHERE posts MATCH 'programming' ORDER BY rank

Order by BM25¶

SELECT * FROM posts WHERE posts MATCH 'programming' ORDER BY bm25(posts)

SELECT highlight(email, 2, '<b>', '</b>') FROM email WHERE email MATCH 'fts5'

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: email)

Highlight¶

1st param: the name of the virtual table.
2nd param: 0-based column index specifying the column from which to search the keywords.
3rd param: left quoting string.
4th param: right quoting string.

SELECT highlight(posts, 3, '<b>', '</b>') from posts WHERE posts MATCH 'programming' ORDER BY rank