[sqlite] Full text serch - Matching all except chosen
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. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full text serch - Matching all except chosen
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'; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full text serch - Matching all except chosen
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
Re: [sqlite] Full text serch - Matching all except chosen
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 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
Re: [sqlite] Full text serch - Matching all except chosen
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
Re: [sqlite] Full text serch - Matching all except chosen
On 31-7-2018 10:52, Luuk wrote: > 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 * +SE > FROM posts > WHERE posts MATCH 'learn NOT text'; > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
