How to write contains a query in SQLite fts3 full-text search

Question

I want to do a full-text search in my index table, which is sqlite fts3.

For instance; dataset {"David Louis", "David Villa", "Diego Costa", "Diego Ribas", "Diego Milito", "Gabriel Milito",

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

In the SQLite documentation, I found this http://www.sqlite.org/fts3.html#section_3 but it only contains a startswith request.

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

I don’t know if this is possible or not. If you can search in sqlite fts3 any help would be appreciated

Answer 1

The FTS index is optimized for word search and supports word prefix search.

There is no index that can help search within words. You should use LIKE '%vid%' (which scans the entire table).

Answer 2

Change your request from

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

For

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