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.

Ellmyer

Most users expect search to be sub-string search, I'd say mostly because of Google. I hope the SQLite guys are working on sub-string support in FTS. – Burden

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.

Internationalist

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

Hachman

Thank you for your answer. It does help but does not solve my problem entirely. – Ellmyer

Answer 3

To get it to work for contains queries, you need to store all suffixes of the terms you want to be able to search. This has the downside of making the database really large, but that can be avoided by compressing the data.

SQLite FTS contains and suffix matches

Charged

Answer 4

You can just do the reversed text insert and search:

CREATE VIRTUAL TABLE products 
USING FTS5(name, brand, code);

INSERT INTO products(name, brand, code)
VALUES('Merlot Cafe', 'Healthy Choice', '07265500101'), (
'tolreM efaC', 'yhtlaeH eciohC', '10100556270');

SELECT * 
FROM products 
WHERE products MATCH 'Ice* OR eci*'

Aram