How to search a substring using FTS5 in SQLite

Question

Suppose I have created a virtual table using FTS5 module in SQLite 3.35 like this:

CREATE VIRTUAL TABLE TrialFTS USING fts5(search);

I have prepopulated the table with data.

Now to search any row that contains the phrase 'Black', I can search using:

select * from tri where TrialFTS MATCH 'black'

To search any row that contains any word starting with 'Black':

select * from tri where TrialFTS MATCH 'black*'

But my problem is- How to search any row using FTS5 that contains 'land' as a substring, so that I can get rows with the words like 'England' or 'Outlander' etc?

I have tried:

select * from TrialFTS where TrialFTS MATCH 'land'

this does not work since 'land' needs to be a complete token/phrase in this case.

I have also tried:

select * from TrialFTS where TrialFTS MATCH '*land'

But this says: unknown special query: land I cannot use simple pattern matching as 'like' keywords. I need to use FTS5 module without any tokenizer such as The Experimental Trigram Tokenizer

Please help me find a way to search substring in sqlite database using FTS5 module and no tokenizer.

Answer 1

Your can use LIKE to filter the result of the search as you would in a normal table.

select * from TrialFTS where search LIKE '%land%';

For LIKE to work, you need to use the name of the column you defined, instead of the Virtual Table Name, and you have to use % instead of *.

You can also mix MATCH and LIKE:

select * from TrialFTS where TrialFTS MATCH 'black' AND search LIKE '%land%';

Note that LIKE will not use the indexing capabilities of FTS5 and it will perform a full table scan of all your stored rows.