FTS5 MATCH using "string*" matches just the exact string

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

FTS5 MATCH using "string*" matches just the exact string

Maziar Parsijani
Hi
I am new here and I am not an expert on sqlite my question is about match
option that I have read from https://sqlite.org/fts5.html but the problem
is when I want to not find the exact word so I use :

SELECT rowid,text

FROM table

WHERE table MATCH 'أعلم*';

And I have to add this that my data is Arabic text.

This method must find words that contains 'أعلم' but it doesn't. What
should I do now.
_______________________________________________
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: FTS5 MATCH using "string*" matches just the exact string

Eric Minbiole-2
> SELECT rowid,text
> FROM table
> WHERE table MATCH 'أعلم*';
>
> And I have to add this that my data is Arabic text.
>
> This method must find words that contains 'أعلم' but it doesn't. What
> should I do now.
>
>
I just tried this in SQLite version 3.24.0 on a Mac, and it seems to work.
Please see my example below. In particular, the first select doesn't have a
*, so it doesn't find the word. The second example includes a trailing *,
so it performs a "starts with" search, and does find the match. (Note that
I simply doubled the letters of your original arabic text to make a longer
word.)

MacBook-II:Programs eric$ ./sqlite3

> SQLite version 3.24.0 2018-06-04 19:24:41
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE VIRTUAL TABLE fts USING fts5(doc);
> sqlite> INSERT INTO fts (rowid, doc) VALUES (1, 'english text');
> sqlite> INSERT INTO fts (rowid, doc) VALUES (2, 'arabic أعلمأعلم');
> sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم';
> sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم*';
> 2
> sqlite>
>

One possible explanation: You mentioned that you want a "contains" search.
However, the trailing asterisk in your example only results in a "begins
with" search. If you really want a "contains" search, you'll need to put
the * at both the beginning and end of the match word. E.g., "WHERE fts
MATCH '*asdf*';

Hope this is helpful.

~Eric
_______________________________________________
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: FTS5 MATCH using "string*" matches just the exact string

sandu
In Arabic you have a different direction of the text. Might be that the
reason?

Sandu

Eric Minbiole <[hidden email]> schrieb am Di., 11. Sep. 2018, 00:23:

> > SELECT rowid,text
> > FROM table
> > WHERE table MATCH 'أعلم*';
> >
> > And I have to add this that my data is Arabic text.
> >
> > This method must find words that contains 'أعلم' but it doesn't. What
> > should I do now.
> >
> >
> I just tried this in SQLite version 3.24.0 on a Mac, and it seems to work.
> Please see my example below. In particular, the first select doesn't have a
> *, so it doesn't find the word. The second example includes a trailing *,
> so it performs a "starts with" search, and does find the match. (Note that
> I simply doubled the letters of your original arabic text to make a longer
> word.)
>
> MacBook-II:Programs eric$ ./sqlite3
> > SQLite version 3.24.0 2018-06-04 19:24:41
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> CREATE VIRTUAL TABLE fts USING fts5(doc);
> > sqlite> INSERT INTO fts (rowid, doc) VALUES (1, 'english text');
> > sqlite> INSERT INTO fts (rowid, doc) VALUES (2, 'arabic أعلمأعلم');
> > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم';
> > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم*';
> > 2
> > sqlite>
> >
>
> One possible explanation: You mentioned that you want a "contains" search.
> However, the trailing asterisk in your example only results in a "begins
> with" search. If you really want a "contains" search, you'll need to put
> the * at both the beginning and end of the match word. E.g., "WHERE fts
> MATCH '*asdf*';
>
> Hope this is helpful.
>
> ~Eric
> _______________________________________________
> 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: FTS5 MATCH using "string*" matches just the exact string

Maziar Parsijani
Thanks for your answer Eric,Sandu
About the first idea of Eric I have to say its correct for me when I do a
test with another fts5 table with no huge rows but my database has that
problem.And about the other possibility I tested that before to set "*"at
the first and end of the match but I have got an error.Here if there is no
problem I will attach an example Arabic text database  if you search for
'أعلم*' it must find 83 rows but it will find 49 which is the same with
"أعلم" results.and if you put "*"at the both sides you will get error.I
don't know if I did something wrong during creation but this is all I have
got.

On Tue, Sep 11, 2018 at 10:20 AM Sandu Buraga <[hidden email]>
wrote:

> In Arabic you have a different direction of the text. Might be that the
> reason?
>
> Sandu
>
> Eric Minbiole <[hidden email]> schrieb am Di., 11. Sep. 2018, 00:23:
>
> > > SELECT rowid,text
> > > FROM table
> > > WHERE table MATCH 'أعلم*';
> > >
> > > And I have to add this that my data is Arabic text.
> > >
> > > This method must find words that contains 'أعلم' but it doesn't. What
> > > should I do now.
> > >
> > >
> > I just tried this in SQLite version 3.24.0 on a Mac, and it seems to
> work.
> > Please see my example below. In particular, the first select doesn't
> have a
> > *, so it doesn't find the word. The second example includes a trailing *,
> > so it performs a "starts with" search, and does find the match. (Note
> that
> > I simply doubled the letters of your original arabic text to make a
> longer
> > word.)
> >
> > MacBook-II:Programs eric$ ./sqlite3
> > > SQLite version 3.24.0 2018-06-04 19:24:41
> > > Enter ".help" for usage hints.
> > > Connected to a transient in-memory database.
> > > Use ".open FILENAME" to reopen on a persistent database.
> > > sqlite> CREATE VIRTUAL TABLE fts USING fts5(doc);
> > > sqlite> INSERT INTO fts (rowid, doc) VALUES (1, 'english text');
> > > sqlite> INSERT INTO fts (rowid, doc) VALUES (2, 'arabic أعلمأعلم');
> > > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم';
> > > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم*';
> > > 2
> > > sqlite>
> > >
> >
> > One possible explanation: You mentioned that you want a "contains"
> search.
> > However, the trailing asterisk in your example only results in a "begins
> > with" search. If you really want a "contains" search, you'll need to put
> > the * at both the beginning and end of the match word. E.g., "WHERE fts
> > MATCH '*asdf*';
> >
> > Hope this is helpful.
> >
> > ~Eric
> > _______________________________________________
> > 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
>
_______________________________________________
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: FTS5 MATCH using "string*" matches just the exact string

Dan Kennedy-4
On 09/11/2018 01:26 PM, Maziar Parsijani wrote:

> Thanks for your answer Eric,Sandu
> About the first idea of Eric I have to say its correct for me when I do a
> test with another fts5 table with no huge rows but my database has that
> problem.And about the other possibility I tested that before to set "*"at
> the first and end of the match but I have got an error.Here if there is no
> problem I will attach an example Arabic text database  if you search for
> 'أعلم*' it must find 83 rows but it will find 49 which is the same with
> "أعلم" results.and if you put "*"at the both sides you will get error.I
> don't know if I did something wrong during creation but this is all I have
> got.

The "*" may only occur following a string in an fts5 expression, not before:

   https://sqlite.org/fts5.html#fts5_prefix_queries

If possible, can you trim the database down to just two rows that
demonstrate the problem and upload it somewhere? i.e. so that it
contains two rows that should both be matched by 'أعلم*', but for which
the current FTS5 only matches one.

Even if the complete database makes the problem obvious to Arabic
readers, it will be quite difficult for non-readers to deal with.

Thanks,
Dan.





>
> On Tue, Sep 11, 2018 at 10:20 AM Sandu Buraga <[hidden email]>
> wrote:
>
>> In Arabic you have a different direction of the text. Might be that the
>> reason?
>>
>> Sandu
>>
>> Eric Minbiole <[hidden email]> schrieb am Di., 11. Sep. 2018, 00:23:
>>
>>>> SELECT rowid,text
>>>> FROM table
>>>> WHERE table MATCH 'أعلم*';
>>>>
>>>> And I have to add this that my data is Arabic text.
>>>>
>>>> This method must find words that contains 'أعلم' but it doesn't. What
>>>> should I do now.
>>>>
>>>>
>>> I just tried this in SQLite version 3.24.0 on a Mac, and it seems to
>> work.
>>> Please see my example below. In particular, the first select doesn't
>> have a
>>> *, so it doesn't find the word. The second example includes a trailing *,
>>> so it performs a "starts with" search, and does find the match. (Note
>> that
>>> I simply doubled the letters of your original arabic text to make a
>> longer
>>> word.)
>>>
>>> MacBook-II:Programs eric$ ./sqlite3
>>>> SQLite version 3.24.0 2018-06-04 19:24:41
>>>> Enter ".help" for usage hints.
>>>> Connected to a transient in-memory database.
>>>> Use ".open FILENAME" to reopen on a persistent database.
>>>> sqlite> CREATE VIRTUAL TABLE fts USING fts5(doc);
>>>> sqlite> INSERT INTO fts (rowid, doc) VALUES (1, 'english text');
>>>> sqlite> INSERT INTO fts (rowid, doc) VALUES (2, 'arabic أعلمأعلم');
>>>> sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم';
>>>> sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم*';
>>>> 2
>>>> sqlite>
>>>>
>>>
>>> One possible explanation: You mentioned that you want a "contains"
>> search.
>>> However, the trailing asterisk in your example only results in a "begins
>>> with" search. If you really want a "contains" search, you'll need to put
>>> the * at both the beginning and end of the match word. E.g., "WHERE fts
>>> MATCH '*asdf*';
>>>
>>> Hope this is helpful.
>>>
>>> ~Eric
>>> _______________________________________________
>>> 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
>>
> _______________________________________________
> 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