sqlite full text wild card search

Question

Can Sqlite FT3 or FT4 do something like

SELECT * FROM MyTable WHERE body MATCH '*qlite'

I know this:

SELECT * FROM MyTable WHERE body MATCH 'Sqlite*'

works but seems like '%like' like operation doesn't work in the full text.

Answer 1

From what I understand it's a limitation of FTS in general, across platforms, that suffix/postfix searches aren't possible.

The best workaround I've seen is to add a column to MyTable called ReverseBody and store the reverse of the Body column in there and add it to the FT index as well. Then you write queries like

select * from MyTable where reversebody match (REVERSE('qlite') + '*')

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