Hi,
Providing better documentation, or a better insight into everything below, I may have figured this out for my needs.
The difficulty is in the queries.
e.g. paragr. 3.3
"the first two queries in the following block will match any document that contains the token "one" immediately followed by the token "two"
What is "immediately followed" ? I have matches where search 1,2 and 3 are scattered in the text.
This will also work with wildcarding but only with the start of the word as prefix.
e.g. "Johns*" -> will find Johnson, but "*son" is not allowed with MATCH.
If you want to do wildcarding as suffix, you need to declare the table at the outset with the "trigram" tokenizer. Like so:
create virtual table if not exists myTable using fts5 (filename,data,register, tokenize='trigram')
Then you can search for LIKE "%son%" , but only one token allowed in the select statement, not 2 or 3. Edit: Unless you use the AND operator, but that is unclear to me.
I found that the MATCH syntax also works on tables initiated as trigram.
There maybe other goodies hidden in this FTS5 extension, but if I add more
possibilities, users may get very confused. :-)
Paul
Providing better documentation, or a better insight into everything below, I may have figured this out for my needs.
The difficulty is in the queries.
sql = f'SELECT filename FROM myTable WHERE datafield MATCH "{search1} {search2} {search3}"'This works, even with 3 AND searchwords , although I find the wording in the SQLite FTS5 Extension confusing.e.g. paragr. 3.3
"the first two queries in the following block will match any document that contains the token "one" immediately followed by the token "two"
What is "immediately followed" ? I have matches where search 1,2 and 3 are scattered in the text.
This will also work with wildcarding but only with the start of the word as prefix.
e.g. "Johns*" -> will find Johnson, but "*son" is not allowed with MATCH.
If you want to do wildcarding as suffix, you need to declare the table at the outset with the "trigram" tokenizer. Like so:
create virtual table if not exists myTable using fts5 (filename,data,register, tokenize='trigram')
Then you can search for LIKE "%son%" , but only one token allowed in the select statement, not 2 or 3. Edit: Unless you use the AND operator, but that is unclear to me.
I found that the MATCH syntax also works on tables initiated as trigram.
There maybe other goodies hidden in this FTS5 extension, but if I add more
possibilities, users may get very confused. :-)
Paul
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu) = French version for 'kiss'.
Better is the enemy of good. (Montesquieu) = French version for 'kiss'.