How to write contains query in SQLite fts3 fulltext search

Question

I want to make fulltext search in my index table which is sqlite fts3.

For example; the data set is { "David Luiz", "David Villa", "Diego Costa", "Diego Ribas", "Diego Milito","Gabriel Milito", }

When I type "vid i" I want to get {"David Luiz", "David Villa"}

In documentation of SQLite I found this http://www.sqlite.org/fts3.html#section_3 but it contains just startswith query.

my query is:
   SELECT *FROM Table WHERE Table MATCH "*vid* *i*"

I dont know it is possible or not. If it is possible to make search in sqlite fts3, any help will be appreciated

Answer 1

The FTS index is optimized for word searches, and supports word prefix searches.

There is no index that can help with searches inside words. You have to use LIKE '%vid%' (which scans the entire table).

Answer 2

Change your query from

SELECT * FROM Table WHERE Table MATCH "*vid* *i*"

To

SELECT * FROM SOME_TABLE WHERE some_column LIKE '%vid%'