Full text serch - Matching all except chosen

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Full text serch - Matching all except chosen

paul tracy
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Full text serch - Matching all except chosen

Luuk
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Full text serch - Matching all except chosen

Luuk
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Full text serch - Matching all except chosen

Chris Locke-3
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Full text serch - Matching all except chosen

Dan Kennedy-4
In reply to this post by paul tracy
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Full text serch - Matching all except chosen

Luuk
In reply to this post by Luuk
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users