Full-text search on the full sqlite map

Question

Can Sqlite FT3 or FT4 be something like

SELECT * FROM MyTable WHERE body MATCH '*qlite' 

I know it:

 SELECT * FROM MyTable WHERE body MATCH 'Sqlite*' 

works, but it looks like '%like' as an operation does not work in full text.

Answer 1

From what I understand, this is a limitation of FTS in general, on different platforms, that suffix / postfix searches are not possible.

The best workaround I've seen is to add a column to MyTable called ReverseBody and keep the back of the Body column and add it to the FT index. Then you write queries like

select * from MyTable where the opposite matches (REVERSE ('qlite') + '*')

I work on SQL Server, so we have REVERSE built in. I don't think SQLite does, but you can add custom functions to do it as descrbed here