Custom tokenchars for apostrophes in SQLite / FTS3 / FTS4 / Android?

Question

My Android app contains a SQLite table (Full Text Search / FTS3) which contains data such as:

Green's Product

In the app, when a user searches for Green's Product, the match is found. But when the user searches for Greens Product (i.e., without the apostrophe), there is no match.

I need the product to be returned regardless of whether the user types the apostrophe or not.

So I have being reading the documentation about FTS3 tokenizers which suggests that I need to specify the apostrophe in the tokencharsargument.

So I have tried re-creating my table like this:

CREATE VIRTUAL TABLE products USING fts3 (product_name TEXT, tokenize=simple "tokenchars='");

but there is still no match when a user searches for Greens Product.

I've also tried:

CREATE VIRTUAL TABLE products USING fts3 (product_name TEXT, tokenize=simple "tokenchars=''"); (double apostrophe)

and

CREATE VIRTUAL TABLE products USING fts4 (product_name TEXT, tokenize=simple "tokenchars='"); (fts4)

and

CREATE VIRTUAL TABLE products USING fts4 (product_name TEXT, tokenize=simple "tokenchars=''"); (double apostrophe and fts4)

but none of them seem to be working for me.

I'm not sure what to try next. And I'm also not sure if I may be coming up against some limitation here due to Android's implementation of SQLite.

Does anyone have any suggestions?

Answer 1

If you add the apostrophe to tokenchars, then it will become part of the word. This means that you must specifiy it to find it.

You actually want to ignore apostrophes, without breaking tokens. There appears to be no built-in option for this; you could either write a custom tokenizer, or just remove such characters before inserting/searching strings.