1

I wonder if the following is possible with fts(3/4) for SQLite. I created a table with some data using fts3.

If i for example search for e* i get everything that starts with ^e But can i also search the index/catalog for the specific words that starts with e?

So that i only get the results of that words back like; elliot, elo,eehh.

Grezly
  • 488
  • 1
  • 6
  • 13

3 Answers3

2

http://www.sqlite.org/fts3.html#section_3

       SELECT * FROM docs WHERE docs MATCH 'lin*';

Will return the docs that contain a word starting with "lin". You only want the words that match, not the context? Maybe the "snippets" feature will help you. And you may find this thread of interest:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg49345.html

Tim
  • 5,371
  • 3
  • 32
  • 41
  • This will return a set of rows which contain lin*, but what i want to achieve, is to return words that are indexed by sqlite. So for example it should return in your example linux, linear,linwhatever...but not the rows that containt lin* – Grezly Dec 23 '10 at 15:24
  • We are typing at same time. See appended info. – Tim Dec 23 '10 at 15:31
  • snippets are what i was looking for.Sqlite indicates which words compare to the input. The only things left is to extract the words, so that we only see the exact word(s) – Grezly Dec 23 '10 at 15:41
  • Good, Donal's answer also provides needed detail. – Tim Dec 23 '10 at 17:14
  • @Grezly: `...what i want to achieve, is to return words that are indexed by sqlite`. If you want to see the list of words that are in the full-text index, see http://www.sqlite.org/fts3.html#fts4aux "Direct Access to the Full-Text Index". – Tim Aug 31 '11 at 11:45
2

The FTS engine provides that information via the offsets virtual function.

SELECT offsets(docs) FROM docs WHERE docs MATCH 'e*';

As the documentation says:

For a SELECT query that uses the full-text index, the offsets() function returns a text value containing a series of space-separated integers. For each term in each phrase match of the current row, there are four integers in the returned list. Each set of four integers is interpreted as follows:

  1. The column number that the term instance occurs in (0 for the leftmost column of the FTS table, 1 for the next leftmost, etc.).
  2. The term number of the matching term within the full-text query expression. Terms within a query expression are numbered starting from 0 in the order that they occur.
  3. The byte offset of the matching term within the column.
  4. The size of the matching term in bytes.

How to extract that information is up to you and how you integrate your code with SQLite.

Community
  • 1
  • 1
Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
0

the documentation of the snippet function is rather vague regarding its 6th parameter because the algorithm uses a best-score approach to find the snippet.

Nevertheless in my application i get a clean extract of the original terms I am looking for with the request :

select snippet(docs,'','','', -1, 1) from docs where docs match 'e*';

It may help in your case ; this in-database solution helped me because i didn't want to extract the terms in code located outside of the db. It works if you are searching for single words (no space, OR, .. in the MATCH query)

Jerome WAGNER
  • 21,986
  • 8
  • 62
  • 77