Change search from fts4 to fts5 for SQLite

General
1

I have a fts4 table which I want to change to fts5. I have to replace docid with rowid. But either I get a syntax error for the select or no result.

Original code:

dim PreparedForSearch as SQLPreparedStatementMBS = SQLiteIndexDBMBS.Prepare("SELECT docid FROM bodyindex WHERE messagebody MATCH ?") 
PreparedForSearch.BindType(0, PreparedForSearch.kTypeString)
SearchString = SearchString.ReplaceAll("""", """""")
dim data as RowSet = PreparedForSearch.SelectSQLMT("'*'" + searchstring + "'*'")

This makes a syntax error. I tried some variations of the last line like

dim data as RowSet = PreparedForSearch.SelectSQLMT("*" + searchstring + "*")

But this also makes a syntax error. The following variation does not make a syntax error but the query doesn’t give any result:

dim data as RowSet = PreparedForSearch.SelectSQLMT(searchstring)

What is the magic combination of * and '?

2

SQL uses % for wildcard character not * or ?. I assume SQLite follows this standard.

dim data as RowSet = PreparedForSearch.SelectSQLMT("%" + searchstring + "%")
3

It looks like you need to change the tokenizer you are using:

https://sqlite.org/fts5.html#full_text_query_syntax

See " 4.3.4. The Trigram Tokenizer" on that page.

4

Thanks, I’ll do some testing with the trigram tokenizer.