8

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

ibrahimyilmaz
  • 18,331
  • 13
  • 61
  • 80
  • you can try this : select * from Table_name where Column_Name like'%vid%' – Amit Jul 10 '14 at 06:32
  • I can write like queries but if it possible and match query do this on behalf of us, I want to learn match query and I want to make search "vid i" not "vid", maybe you simplfy my query but it is just example and I want to get texts that contains both vid and i together like da"VID" v"I"lla da"VID" lu"I"z – ibrahimyilmaz Jul 10 '14 at 06:34
  • Post your related code of query – Amit Jul 10 '14 at 06:37

2 Answers2

10

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

CL.
  • 173,858
  • 17
  • 217
  • 259
3

Change your query from

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

To

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

RPB
  • 16,006
  • 16
  • 55
  • 79