Search code examples
iphoneiossqlitefts3

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

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.

Solution

  • 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.