Sqlite FTS3/4 only NOT search

Question

How to select all rows except unneeded from SQLite FTS3 or FTS4 table?

select * from table where table match 'column:NOT phrase'
select * from table where table match 'column:-phrase'
select * from table where table match 'column:* NOT phrase'

not working as expected.

Is your library compiled for the standard or the enhanced query syntax? — CL., Dec 19 '16 at 22:10
It's a standard library from NuGet - System.Data.Sqlite Core. I think, it's compiled with standard syntax. I can compile it manually, but please tell me, what is "enchanced syntax" and which queries of enchanced syntax will help me? — devspec, Dec 20 '16 at 10:56
I did not write that extra "c". Read the [fine documentation](http://www.sqlite.org/fts3.html#full_text_index_queries). — CL., Dec 20 '16 at 11:55

score 1 · Answer 1 · answered Dec 20 '16 at 11:58

This is not possible with either NOT or -, because the negation must not be the only operator in the FTS query.

You have to search for the phrase, and exclude those rows from the result with normal SQL:

SELECT ...
FROM MyTable
WHERE ID NOT IN (SELECT docid
                 FROM MyTableFTS
                 WHERE MyTableFTS MATCH 'phrase');

Sqlite FTS3/4 only NOT search

1 Answers1