ends with (suffix) and contains string search using MATCH in SQLite FTS

Question

I am using SQLite FTS extension in my iOS application. It performs well but the problem is that it matches only string prefixes (or starts with keyword search).

i.e.

This works:

SELECT FROM tablename WHERE columnname MATCH 'searchterm*'

but following two don't:

SELECT FROM tablename WHERE columnname MATCH '*searchterm'


SELECT FROM tablename WHERE columnname MATCH '\*searchterm\*'

Is there any workaround for this or any way to use FTS to build a query similar to LIKE '%searchterm%' query.

EDIT:

As pointed out by Retterdesdialogs, storing the entire text in reverse order and running a prefix search on a reverse string is a possible solution for ends with/suffix search problem, which was my original question, but it won't work for 'contains' search. I have updated the question accordingly.

Answer 1

In my iOS and Android applications, I have shied away from FTS search for exactly the reason that it doesn't support substring matches due to lack of suffix queries.

The workarounds seem complicated.

I have resorted to using LIKE queries, which while being less performant than MATCH, served my needs.

Answer 2

The workaround is to store the reverse string in an extra column. See this link (its not exactly the same it should give a idea):

Search Suffix using Full Text Search