CODEWITHSUNDEEP
sqlitefull-text-search
Noumenon
Noumenon

Reputation: 6452

How can you exclude a search term in an SQLite fulltext search?

I am using DB Browser for SQLite. The documentation for SQLite's fts3 says "FTS is primarily designed to support Boolean full-text queries". I built a virtual table using fts4 and successfully executed a few WHERE ... MATCH queries. But the following attempts give errors:

SELECT id FROM histsearch WHERE id MATCH ("-1456" IN BOOLEAN MODE);
SELECT id FROM histsearch WHERE NOT EXIST id MATCH ("1457");

Is the problem in DB Browser or in SQLite? How else can I write this query so it will work?

Upvotes: 0

Views: 894

Answers (1)

Noumenon
Noumenon

Reputation: 6452

SQLite's full text service (fts3) basically offers Boolean Mode by default, no search modifier needed. DB Browser uses fts's standard query syntax, so NOT is not supported. To exclude a term, do something like 

SELECT * FROM indexed WHERE indexed MATCH 'sqlite -database';

Edit: however, you cannot only exclude search terms in fulltext search:

An FTS query may not consist entirely of terms or term-prefix queries with unary "-" operators attached to them.

You'll have to use NOT LIKE for that.

Upvotes: 1

Related Questions