Stack Overflow
Stack Overflow

Questions tagged [fts4]

FTS3 and FTS4 are an SQLite virtual table modules that allows users to perform full-text searches on a set of documents.

The most common (and effective) way to describe full-text searches is "what Google, Yahoo and Altavista do with documents placed on the World Wide Web". Users input a term, or series of terms, perhaps connected by a binary operator or grouped together into a phrase, and the full-text query system finds the set of documents that best matches those terms considering the operators and groupings the user has specified.

103 questions
1
vote
0 answers

Full text search on Android

I have some misundestanding concerning to FTS, I'd be thanksful if someone will be able to help me. GOAL: Full text search using MATCH function. Problem: Unable to do search by extended ASCII characters like: '#¿®£$ and etc. Details: There are three…
AinisSK
  • 306
  • 1
  • 10
1
vote
2 answers

Sqlite full text search keyword 'Match' not returning any result

I am working on full text search on sqlite using fts4. In my DB I have a normal table Notes and it contains 33 records. I created a virtual table using fts4 like this CREATE VIRTUAL TABLE t3 USING fts4(content="Notes", user_notes); I am querying it…
S.J
  • 3,063
  • 3
  • 33
  • 66
1
vote
1 answer

Sqlite FTS3/4 only NOT search

How to select all rows except unneeded from SQLite FTS3 or FTS4 table? select * from table where table match 'column:NOT phrase' select * from table where table match 'column:-phrase' select * from table where table match 'column:* NOT phrase' not…
devspec
  • 49
  • 10
1
vote
1 answer

Using MATCH with AND/OR operators with parameters doesn't work in SQLite

I'm having problem with executing select statement while using match and and/or operators while using parameters. The statement looks like this: SELECT ColumnName1, ColumnName2 FROM TableName WHERE TableName MATCH 'ColumnName1:@param1 AND…
fuzzomorphism
  • 79
  • 1
  • 10
1
vote
1 answer

SQLite: query with comparison operators in a FTS4 enabled table

SQLite version: 3.12.2 Using this expression I created a table to employ the possibility of fast text queries: CREATE VIRTUAL TABLE sentence_tbl USING FTS4 (WordsNo INTEGER, Sentence TEXT); Note 1: WordsNo is the number of words in the Sentence…
wiki
  • 1,877
  • 2
  • 31
  • 47
1
vote
0 answers

Creating dynamic FTS4 Tables in Sqlite

I have been working on making my sqlite DB more dynamic through the use of views. This has helped in all cases except the full text search tables. According to the SQLite FTS4 documentation (https://www.sqlite.org/fts3.html#section_6_2_2), there…
N1ght
  • 53
  • 9
1
vote
1 answer

FTS4 query's error in Android

I create a fts4 table on PC using: CREATE VIRTUAL TABLE main USING fts4(word, definition, notindexed=definition); In SQLite Studio i queried a statement, which ran ok: SELECT rowid AS _id, word, definition FROM main WHERE word MATCH 'はな*'; When i…
tuanna
  • 767
  • 1
  • 6
  • 13
1
vote
2 answers

Sqlite FTS4 token prefix for phrase query

I am trying to query a Sqlite database taking into account 2 fields, say title and author, with the following query: SELECT * FROM books WHERE title MATCH 'Author:* *' It works when <author> and <title> do not contain spaces (i.e. a…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/sqlite" class="post-tag grid--cell" title="show questions tagged 'sqlite'" rel="tag">sqlite</a> <a href="../../questions/tagged/full-text-search" class="post-tag grid--cell" title="show questions tagged 'full-text-search'" rel="tag">full-text-search</a> <a href="../../questions/tagged/fts4" class="post-tag grid--cell" title="show questions tagged 'fts4'" rel="tag">fts4</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Jan 28 '15 at 15:36">asked Jan 28 '15 at 15:36</time> <a href="../../users/1847857/niculare" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/1847857.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="niculare" /> </a> <div class="s-user-card--info"> <a href="../../users/1847857/niculare" class="s-user-card--link">niculare</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">3,629</li> <li class="s-award-bling s-award-bling__gold" title="1 gold badge">1</li> <li class="s-award-bling s-award-bling__silver" title="25 silver badge">25</li> <li class="s-award-bling s-award-bling__bronze" title="39 bronze badge">39</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-27380873"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status "> <strong>0</strong> answers </div> </div> </div> <div class="summary"> <h3><a href="../../questions/27380873/highlighting-bold-text-in-sqlite-full-text-search" class="question-hyperlink">highlighting/bold text in sqlite full text search</a></h3> <div class="excerpt">I want to know whether SQLite FTS3 and FTS4 support hit highlighting? Documentation says snippet function can be used for text formatting. I implemented the same but it does not return formatted text. My detailed code in an unanswered questions is…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/android" class="post-tag grid--cell" title="show questions tagged 'android'" rel="tag">android</a> <a href="../../questions/tagged/sqlite" class="post-tag grid--cell" title="show questions tagged 'sqlite'" rel="tag">sqlite</a> <a href="../../questions/tagged/full-text-search" class="post-tag grid--cell" title="show questions tagged 'full-text-search'" rel="tag">full-text-search</a> <a href="../../questions/tagged/fts3" class="post-tag grid--cell" title="show questions tagged 'fts3'" rel="tag">fts3</a> <a href="../../questions/tagged/fts4" class="post-tag grid--cell" title="show questions tagged 'fts4'" rel="tag">fts4</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Dec 09 '14 at 14:07">asked Dec 09 '14 at 14:07</time> <a href="../../users/4161902/mansoor" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/4161902.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="Mansoor" /> </a> <div class="s-user-card--info"> <a href="../../users/4161902/mansoor" class="s-user-card--link">Mansoor</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">67</li> <li class="s-award-bling s-award-bling__bronze" title="11 bronze badges">11</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-22806564"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status answered-accepted"> <strong>1</strong> answer </div> </div> </div> <div class="summary"> <h3><a href="../../questions/22806564/fts-query-similar-to-like-a" class="question-hyperlink">FTS query similar to LIKE 'a%'?</a></h3> <div class="excerpt">Is it possible to leverage SQLite FTS index capabilities to get result similar to LIKE 'a%' query? There is MATCH 'a*' but its not the same, for example: 'hello world' 'say hello' LIKE 'he%' would return only first record (this is what I…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/sqlite" class="post-tag grid--cell" title="show questions tagged 'sqlite'" rel="tag">sqlite</a> <a href="../../questions/tagged/full-text-search" class="post-tag grid--cell" title="show questions tagged 'full-text-search'" rel="tag">full-text-search</a> <a href="../../questions/tagged/fts4" class="post-tag grid--cell" title="show questions tagged 'fts4'" rel="tag">fts4</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Apr 02 '14 at 09:09">asked Apr 02 '14 at 09:09</time> <a href="../../users/83938/nik" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/83938.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="Nik" /> </a> <div class="s-user-card--info"> <a href="../../users/83938/nik" class="s-user-card--link">Nik</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">9,063</li> <li class="s-award-bling s-award-bling__gold" title="7 gold badges">7</li> <li class="s-award-bling s-award-bling__silver" title="66 silver badges">66</li> <li class="s-award-bling s-award-bling__bronze" title="81 bronze badges">81</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-21377073"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status answered-accepted"> <strong>2</strong> answers </div> </div> </div> <div class="summary"> <h3><a href="../../questions/21377073/ios-sqlite-fts-extensions-no-such-module-fts4-error" class="question-hyperlink">ios sqlite fts extensions "no such module fts4" error</a></h3> <div class="excerpt">I am trying to set an index database with CoreData and FMDB according to this tutorial (here) I am beginner in ios, so I may made something wrong when adding SQLITE_ENABLE_FTS3 SQLITE_ENABLE_FTS3_PARENTHESIS macros. I added them to Other C…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/ios" class="post-tag grid--cell" title="show questions tagged 'ios'" rel="tag">ios</a> <a href="../../questions/tagged/sqlite" class="post-tag grid--cell" title="show questions tagged 'sqlite'" rel="tag">sqlite</a> <a href="../../questions/tagged/xcode5" class="post-tag grid--cell" title="show questions tagged 'xcode5'" rel="tag">xcode5</a> <a href="../../questions/tagged/fts4" class="post-tag grid--cell" title="show questions tagged 'fts4'" rel="tag">fts4</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Jan 27 '14 at 09:23">asked Jan 27 '14 at 09:23</time> <a href="../../users/3137158/utkus" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/3137158.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="UtkuS" /> </a> <div class="s-user-card--info"> <a href="../../users/3137158/utkus" class="s-user-card--link">UtkuS</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">234</li> <li class="s-award-bling s-award-bling__silver" title="3 silver badges">3</li> <li class="s-award-bling s-award-bling__bronze" title="10 bronze badges">10</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-19634458"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status answered-accepted"> <strong>1</strong> answer </div> </div> </div> <div class="summary"> <h3><a href="../../questions/19634458/sqlite-regular-table-and-table-for-fts" class="question-hyperlink">SQLite regular table and table for fts</a></h3> <div class="excerpt">I have table news (id, news_id, news_title) and I creat FTS table: CREATE VIRTUAL TABLE news_search USING fts4 (news_title, tokenize=porter); I use trigger to keep table NEWS and news_search in sync: CREATE TRIGGER IF NOT EXISTS…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/sqlite" class="post-tag grid--cell" title="show questions tagged 'sqlite'" rel="tag">sqlite</a> <a href="../../questions/tagged/full-text-search" class="post-tag grid--cell" title="show questions tagged 'full-text-search'" rel="tag">full-text-search</a> <a href="../../questions/tagged/fts3" class="post-tag grid--cell" title="show questions tagged 'fts3'" rel="tag">fts3</a> <a href="../../questions/tagged/fts4" class="post-tag grid--cell" title="show questions tagged 'fts4'" rel="tag">fts4</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Oct 28 '13 at 12:10">asked Oct 28 '13 at 12:10</time> <a href="../../users/1206433/magog" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/1206433.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="Magog" /> </a> <div class="s-user-card--info"> <a href="../../users/1206433/magog" class="s-user-card--link">Magog</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">485</li> <li class="s-award-bling s-award-bling__silver" title="4 silver badges">4</li> <li class="s-award-bling s-award-bling__bronze" title="13 bronze badges">13</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-19306172"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status answered-accepted"> <strong>2</strong> answers </div> </div> </div> <div class="summary"> <h3><a href="../../questions/19306172/how-to-shift-the-displayed-suggestions-text-to-the-matched-word" class="question-hyperlink">How to shift the displayed suggestions text to the matched word</a></h3> <div class="excerpt">I don't know if my question well written, sorry for it, but here's what I need. I have an Android app with a search activity and my own dictionary for suggestions. When I show a suggestion I use the item's name in the first row, and a second row…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/android" class="post-tag grid--cell" title="show questions tagged 'android'" rel="tag">android</a> <a href="../../questions/tagged/sqlite" class="post-tag grid--cell" title="show questions tagged 'sqlite'" rel="tag">sqlite</a> <a href="../../questions/tagged/fts4" class="post-tag grid--cell" title="show questions tagged 'fts4'" rel="tag">fts4</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Oct 10 '13 at 21:02">asked Oct 10 '13 at 21:02</time> <a href="../../users/1464539/dpedrinha" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/1464539.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="Dpedrinha" /> </a> <div class="s-user-card--info"> <a href="../../users/1464539/dpedrinha" class="s-user-card--link">Dpedrinha</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">3,741</li> <li class="s-award-bling s-award-bling__gold" title="3 gold badges">3</li> <li class="s-award-bling s-award-bling__silver" title="38 silver badges">38</li> <li class="s-award-bling s-award-bling__bronze" title="57 bronze badges">57</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-14065647"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status "> <strong>1</strong> answer </div> </div> </div> <div class="summary"> <h3><a href="../../questions/14065647/why-does-sqlite-full-text-search-fts4-treat-angle-brackets-differently-in-a-co" class="question-hyperlink">Why does SQLite full-text search (FTS4) treat angle brackets differently in a compound search?</a></h3> <div class="excerpt">I have an SQLite database using FTS4. It is used to store emails with message id's of the form: Searching for messages using the FTS MATCH syntax, I get a result from: SELECT rowid FROM emails WHERE emails MATCH '<8200@comms.io>' This returns the…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/sqlite" class="post-tag grid--cell" title="show questions tagged 'sqlite'" rel="tag">sqlite</a> <a href="../../questions/tagged/full-text-search" class="post-tag grid--cell" title="show questions tagged 'full-text-search'" rel="tag">full-text-search</a> <a href="../../questions/tagged/fts3" class="post-tag grid--cell" title="show questions tagged 'fts3'" rel="tag">fts3</a> <a href="../../questions/tagged/fts4" class="post-tag grid--cell" title="show questions tagged 'fts4'" rel="tag">fts4</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Dec 28 '12 at 06:37">asked Dec 28 '12 at 06:37</time> <a href="../../users/134919/roderick" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/134919.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="Roderick" /> </a> <div class="s-user-card--info"> <a href="../../users/134919/roderick" class="s-user-card--link">Roderick</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">1,205</li> <li class="s-award-bling s-award-bling__silver" title="11 silver badges">11</li> <li class="s-award-bling s-award-bling__bronze" title="24 bronze badges">24</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="mln24"> <div class="question-summary" id="question-summary-13504620"> <div class="statscontainer"> <div class="stats"> <div class="vote"> <div class="votes"> <span class="vote-count-post"><strong>1</strong></span> <div class="viewcount">vote</div> </div> </div> <div class="status answered-accepted"> <strong>1</strong> answer </div> </div> </div> <div class="summary"> <h3><a href="../../questions/13504620/handling-html-escaping-with-sqlite-s-fts-snippet-function" class="question-hyperlink">handling html escaping with sqlite's FTS snippet function</a></h3> <div class="excerpt">I'm using sqlite's full-text-search support to store documents that may contain &, < and > characters. I intended to use the snippet function to highlight the matches for an html results page, but I don't see an obvious way to escape the text before…</div> <div class="grid ai-start jc-space-between fw-wrap"> <div class="grid gs4 fw-wrap tags "> <a href="../../questions/tagged/python" class="post-tag grid--cell" title="show questions tagged 'python'" rel="tag">python</a> <a href="../../questions/tagged/sqlite" class="post-tag grid--cell" title="show questions tagged 'sqlite'" rel="tag">sqlite</a> <a href="../../questions/tagged/full-text-search" class="post-tag grid--cell" title="show questions tagged 'full-text-search'" rel="tag">full-text-search</a> <a href="../../questions/tagged/fts4" class="post-tag grid--cell" title="show questions tagged 'fts4'" rel="tag">fts4</a> </div> <div class="started mt0"> <div class="s-user-card s-user-card"> <time class="s-user-card--time" datetime="asked Nov 22 '12 at 01:20">asked Nov 22 '12 at 01:20</time> <a href="../../users/1009916/strcat" class="s-avatar s-avatar__32 s-user-card--avatar"> <img class="s-avatar--image" src="../../users/profiles/1009916.webp" data-jdenticon-width="32" data-jdenticon-height="32" data-jdenticon-value="strcat" /> </a> <div class="s-user-card--info"> <a href="../../users/1009916/strcat" class="s-user-card--link">strcat</a> <ul class="s-user-card--awards"> <li class="s-user-card--rep" title="reputation score">5,376</li> <li class="s-award-bling s-award-bling__gold" title="1 gold badge">1</li> <li class="s-award-bling s-award-bling__silver" title="27 silver badge">27</li> <li class="s-award-bling s-award-bling__bronze" title="31 bronze badge">31</li> </ul> </div> </div> </div> </div> </div> </div> </div> <div class="s-pagination pager fr"> <a class="s-pagination--item" href="../../questions/tagged/fts4_page=3" rel="prev" title="Go to page 3">Prev </a> <a class="s-pagination--item" href="../../questions/tagged/fts4_page=1" rel="" title="Go to page 1">1</a> <a class="s-pagination--item" href="../../questions/tagged/fts4_page=2" rel="" title="Go to page 2">2</a> <a class="s-pagination--item" href="../../questions/tagged/fts4_page=3" rel="" title="Go to page 3">3</a> <div class="s-pagination--item is-selected">4</div> <a class="s-pagination--item" href="../../questions/tagged/fts4_page=5" rel="" title="Go to page 5">5</a> <a class="s-pagination--item" href="../../questions/tagged/fts4_page=6" rel="" title="Go to page 6">6</a> <a class="s-pagination--item" href="../../questions/tagged/fts4_page=7" rel="" title="Go to page 7">7</a> <a class="s-pagination--item" href="../../questions/tagged/fts4_page=5" rel="next" title="Go to page 5"> Next</a> </div> </div> </div> </div> </div> <script src="../../static/js/stack-icons.js"></script> <script src="../../static/js/fromnow.js"></script> </body> </html>