Full-Text-Search SQLite


i read Seth Verrinders Article “Searching SQLite” in xDeveloper-Magazine 11.4, i understood to create a virtual Table for using “fts4” for the full-text-search. But i don’t know how to use.


i have 2 Tables:

  • Names (id, givennames, surnameid)
  • Surnames (id, surname)

If i enter a new Name, i wanna check, if the Surname exists und want to link the existing surname-id. OK, i can also use the Sql-String “WHERE blabla LIKE …”. Buts thats not the best variant for myself.

Can anyone explain the operation of fts4 or the implementation?

Thank you

Maybe these will help:

Thank you Paul. And when i add new Surnames i have to renew the Virtual Table?

INSERT INTO BookSearch SELECT id, Title, Desc FROM Book; …Why not insert into the Book-Table? Only into the virtual? If i close my Prog, the new Data will lost, or?

Yes, that is my understanding.

FTS only works with the virtual table, so it has to have all the data you want to search. If you are regularly adding new data, then you’d have to add it in both places.

A virtual table is not temporary, it is “different”. You’ll want to read the official SQLite docs for all the specifics, though.

Any fields from the Book table that you want included in the full text search must be inserted into the FTS table as well. Easiest way to do it is to define an insert trigger on the Book table to insert the desired fields into FTS. You’ll also want to define an update trigger and delete trigger (to make sure records in FTS get updated/deleted if the corresponding records in the Book table get updated/deleted).