Android Sqlite Fts Not Operation

I'm trying to order a fast text search so that exact matches are first and partial matches are last. I've created a query that works in SQLiteStudio: SELECT value, 1 AS _order FRO

Solution 1:

Android FTS uses Standard Query Syntax, Not Enhanced.

As such, it does not recognize "NOT" or "AND" as operators. It's actually trying to match them to the text of your db columns. Which is why on the Android, your only match came from an entry with "not" in the actual text.

You have to use "-" for NOT and blank space for AND. Thus, your syntax should look like:

WHERE glossfts.value MATCH 'dog* -dog'

http://www.sqlite.org/fts3.html

Solution 2:

Apparently, the SQLite on that Android device has be compiled with a different FTS query syntax.

Check the output of PRAGMA compile_options; for ENABLE_FTS3_PARENTHESIS, and adjust your queries accordingly.