Android Sqlite FTS NOT Operation

Question

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 FROM glossfts
WHERE glossfts.value MATCH 'dog'
UNION
SELECT value, 2 AS _order FROM glossfts
WHERE glossfts.value MATCH 'dog* NOT dog'
ORDER BY _order

So the result would be

Beware of dog                                     1
Disliked by everybody, not even a dog will eat    1
Bad dog                                           1
Creed, dogma                                      2
Dogs                                              2
Dogwood                                           2

And that works great but when I use the same query in android I only get

Beware of dog                                     1
Disliked by everybody, not even a dog will eat    1
Bad dog                                           1
Disliked by everybody, not even a dog will eat    2

back as it seems to be interpreting the:

MATCH 'dog* NOT dog'

as

MATCH 'dog* not dog'

Whats going on?

So your problem is that when you search for handcar, SQLite finds dog? Anyway, instead of UNION and _order, you could just use UNION ALL. — CL.

sean262 none sean262 none · Accepted Answer · 2013-06-26T08:13:47

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

Android Sqlite FTS NOT Operation

2 Answers