Full text search matching only specific words?

Question

I recently started using SqLite, so I'm relatively new. I am trying to use the full-text search function to find crude matches for a chat robot. Basically, I want to match as many keywords as possible, but not necessarily all of them. The results should be sorted depending on how many keywords were found in the phrase and how strictly their query is ordered. In other words, the ordering does not have to be accurate, but the closer it is, the higher the result. Similarly, even if only one or two words are found in a phrase, it must match, but the rank is higher than the more words that are present. I read the link and I see the NEAR instruction and the matchinfo function, as well as an example of how to use it, but I cannot figure out how to apply this knowledge to my specific problem. Anyone have any suggestions?

Thanks in advance for your help.

Answer 1

I was recently informed that this is not possible on the SqLite mailing list. The closest I came to a solution was to cut out stop words like a search engine, and also use the porter streamer algorithm to further generalize the queries. First, search for a complete set of keywords (naturally without punctuation and the like), then search for the same set of keywords using the stem, then search for the same set, but with the words disabled, and finally search for the same divided subset with the completion applied, it seems, gives a reasonable approximation from better to worse. Of course, as soon as some matches are found, the more general queries that follow in the chain above are not executed.

Answer 2

This is a sql query that you can use ...

Select * From Tablename Where Yourfield = '"+textbox.text(or any data)+"%' 

this will give you all the data of this field starting with leter or number in text files or whatever u want

for example: - u enter t this will give T tea Tisha

from the numbers, too, u enter 1 u will be gwt 1 112 1 13

Answer 3

It looks like you can get this information using the offsets helper function. Here is a link to more complete documentation:

4.1. Offset function

Basically you add a function to your query and it will return the offsets in the document.

 SELECT offsets(data) FROM data ... 

Each result is a list, separated by a space of 4 integers. The third column is the byte offset of the matching term within the column. You should be able to create a solution with this information.

Here is a transcript of some search queries.

 sqlite> create virtual table data using fts4(body); sqlite> insert into data(body) values('the quick brown fox jumps over the lazy dog'); sqlite> insert into data(body) values('the lazy brown fox quickly jumps over the lazy dog'); sqlite> select * from data where body match 'lazy'; the quick brown fox jumps over the lazy dog the lazy brown fox quickly jumps over the lazy dog sqlite> select rowid,offsets(data) from data where body match 'brown'; 1|0 0 10 5 2|0 0 9 5 sqlite> select rowid,offsets(data) from data where body match 'lazy'; 1|0 0 35 4 2|0 0 4 4 0 0 42 4 

So, if you want to search brown and lazy, both of these documents are the same.

In the first document, brown is 10, and lazy is 35. They are 25.

For the second document, brown is 9, and lazy is 4 and 42. They are 5 and 33 from each other.

They also refer to the ordering of the predicate, although it does not work when I try to combine two terms in the same query. I'm not sure if I misunderstand, or if I just don't know the right semantics.

I suspect that there are some SQL pivot syntagans that can be used to perform all ranking calculations in sqlite. Getting results from sqlite and just doing the math ranking itself are probably more serviceable.