If you want anything except "cat" then you can use the less than and greater than comparison - <> . select * from table where field <> 'cat'
This equates to "select all records where the value in the field column is less than and is greater than 'cat'. SQL allows you to search for less than and greater than on alphabetic characters - so "select * from table where field < 'cat' " means "select all the records from the table (called 'table' in my example) where the field (called 'field') has values less (alphabetically) than 'cat' - , so 'apple', 'banana', and 'cabbage', but not 'dog' or 'elephant' - they are greater (alphabetically). Hope this helps. Chris On Tue, Jul 31, 2018 at 9:58 AM Luuk <luu...@gmail.com> wrote: > On 31-7-2018 10:52, Luuk wrote: > > On 31-7-2018 07:25, 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 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. > >> Any insight would be greatly appreciated. > > From: http://www.sqlitetutorial.net/sqlite-full-text-search/ > > > > For example, to get the documents that match the |learn| phrase but > > doesn’t match the |FTS5| phrase, you use the |NOT| operator as follows: > > > > LECT * > > FROM posts > > WHERE posts MATCH 'learn NOT text'; > > > > > > But this should work too (untested): > > SELECT * > > FROM posts > > WHERE NOT posts MATCH 'text' AND posts MATCH 'learn'; > > > According to this docs it should, (so no need to test :-):-)) > https://www.sqlite.org/lang_expr.html > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
