SQlite3 fts negate search

Question

Hi and thanks for looking!

I'm searching for articles which contain certain strings with full text search, something like this:

SELECT * FROM Articles_fts WHERE body MATCH 'monkey OR banana OR "hungry gorilla"';

My real search is much longer (22 terms) and probably not the smartest way of doing this but it's working so no problems there.

What I need help with... Now I want to return the OPPOSITE of what this search returns. In other words I want all the articles that don't contain monkey, banana, or "hungry gorilla".

Thanks very much in advance!

CL. CL. · Accepted Answer · 2015-02-26T15:27:20

NOT is available only when SQLite was compiled with the enhanced FTS query syntax enabled.

Otherwise, you can use SQL set operations to negate the result:

SELECT *
FROM Articles
WHERE id NOT IN (SELECT docid
                 FROM Articles_fts
                 WHERE body MATCH '...')

SQlite3 fts negate search

1 Answers