On 07/31/2018 12:25 PM, paul tracy wrote:
Forgive me if this is the wrong way to do this but I'm a newbie.
I am using version 3.24.0 with FTS5
Is there a way to perform a full text search that returns every row except
records matching a specified query string?
The following does not work because of a syntax error as the syntax requires a
query string before the NOT operator: … MATCH 'NOT blah';The following also
does not work because the * operand cannot be used by itself: … MATCH '* NOT
blah';
In desperation I tried the following which returned data but a seemingly random set of
data: MATCH NOT 'blah';
I think that's interpreted as "MATCH (NOT 'blah')", which is equivalent
to "MATCH 1". So probably returned all the rows featuring a "1" token.
I wound up using something like this …
SELECT * FROM mytable WHERE id NOT IN (SELECT id from FullTextIndex WHERE
FullTextIndex MATCH 'blah');
I think this is much slower on large databases than a full FTS-based query but
maybe I'm wrong and this is as fast as it gets.
I think that is the best you can do. The FTS index does not have a list
of all document ids to exclude the list of ids that match "blah" from.
You could force it to store such a list, then contrive of a query that
used it. e.g. add "commontoken" to the start of all entries and then
query for:
"commontoken NOT blah"
Or you could create a custom tokenizer that just pretended each entry
had a common token at the start of it.
https://www.sqlite.org/fts5.html#custom_tokenizers
But otherwise, the underlying data structure just doesn't support that
type of query.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
