UserGuide:SQLite Full Text Searching

SQLite supports full text searching, but what is it? Full text searching is a fast way to look for specific words in text columns of a database table. Without full text searching, you would typically search a text column using the LIKE command. For example, you might use this SQL command to find all books that have "cat" in the description:

SELECT Title FROM Book WHERE Desc LIKE '%cat%';

But this select actually finds rows where the Desc column has the letters "cat" in it, even if it is in another word, such as "cater". Also, using LIKE does not make use of any indexing on the table. The table has to be scanned row by row to see if it contains the value, which can be slow for large tables.

Demonstrating SQLite using FTS5

Full text seach is a way to avoid these two issues. With SQLite, you enable full text search by creating what is called a "virtual table" using one of the FTS engines included with SQLite: FTS4 or FTS5. FTS5 support was added to the SQLiteDatabase class with Xojo 2016 Release 3 and has more advanced searching features, including ranking and highlighting of results and is what is described here.

To create an FTS5 virtual table, you use the SQL CREATE VIRTUAL TABLE command. This SQL command creates a virtual BookSearch table using FTS5:

CREATE VIRTUAL TABLE BookSearch USING fts5(ID, Title, Desc);

This SQL creates a "fake" table that is hooked up to the full text search engine. You can now populate this table with the data you want to search, usually copying it from data in a normal table with SQL like this:

INSERT INTO BookSearch SELECT ID, Title, Desc FROM Book;

With the data in place, you are now able to search it using a SELECT statement with the special MATCH keyword. For example, this SQL searches for all books that have the word "cat" in the any of the columns:

SELECT Title FROM BookSearch WHERE BookSearch MATCH 'cat';

And you can also search specific columns in the FTS table by prefixing the column name to the match criteria. This SQL searches just the Desc column for "cat":

SELECT Title FROM BookSearch WHERE BookSearch MATCH 'Desc:cat';

To rank your search results by relevance (most relevant to least relevant) you can use an ORDER BY with the special rank value. This SQL ranks search results:

SELECT Title FROM BookSearch WHERE BookSearch MATCH 'cat' ORDER BY rank;

You can do wildcard searching with the "*" character. This SQL searches the column for all text that starts with "prog", so it will find "program", "programming", etc:

SELECT Title FROM BookSearch WHERE BookSearch MATCH 'prog*';

As a final tip, you can even highlight the matched text in the results by using the highlight function. This SQL uses brackets to highlight the results found in the Desc column (the 2 indicates the third column, which is Desc):

SELECT Title, highlight(BookSearch, 2, '<', '>') AS HighlightDesc FROM BookSearch WHERE BookSearch MATCH 'cat';

Use the SQLSelect method of the SQLiteDatabase class to send any of the above commands to a SQLite database.

There are even more advanced search capabilities you can perform, which you can read about in the official SQLite docs for FTS5: SQLite FTS5

FTS 3 and FTS4 also remain available. You can find more information about them on the SQLite site: SQLite FTS3 and FTS4

SQLite New Features

Example Projects

These example projects demonstrate how to use full text searching:

Examples/Databases/SQLite/FTS5Example
Examples/Databases/SQLite/FTSExample

UserGuide

SQLite Full Text Searching

From Xojo Documentation

Contents

Example Projects

See Also