{"id":1207,"date":"2016-12-07T14:08:26","date_gmt":"2016-12-07T07:08:26","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1207"},"modified":"2022-04-03T15:03:42","modified_gmt":"2022-04-03T08:03:42","slug":"sqlite-full-text-search","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-full-text-search\/","title":{"rendered":"Getting Started with SQLite Full-text Search"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite full-text search feature by using the FTS5 virtual table module.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQLite full-text search<\/h2>\r\n\r\n\r\n\r\n

A virtual table is a custom extension to SQLite. A virtual table is like a normal table. The difference between a virtual table and a normal table is where the data come from i.e., when you process a normal table, SQLite accesses the database file to retrieve data. However, when you access a virtual table, SQLite calls the custom code to get the data. The custom code can have specified logic to handle certain tasks such as getting data from multiple data sources.<\/p>\r\n\r\n\r\n\r\n

To use full-text search in SQLite, you use FTS5 virtual table module.<\/p>\r\n\r\n\r\n\r\n

The following CREATE VIRTUAL TABLE<\/code> statement creates an FTS5 table with two columns:<\/p>\r\n\r\n\r\n

CREATE<\/span> VIRTUAL<\/span> TABLE<\/span> table_name \r\nUSING<\/span> FTS5(column1,column2...);\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Notice that you cannot add types, constraints, or PRIMARY KEY<\/code><\/a> declaration in the CREATE VIRTUAL TABLE<\/code> statement for creating an FTS5 table. If you do so, SQLite will issue an error.<\/p>\r\n\r\n\r\n\r\n
Like creating a normal table without specifying the primary key column<\/a>, SQLite adds an implicit rowid<\/code> column to the FTS5 table.<\/p>\r\n\r\n\r\n\r\n
The following example creates an FTS5 table named posts<\/code> with two columns title<\/code> and body<\/code>.<\/p>\r\n\r\n\r\n
CREATE<\/span> VIRTUAL<\/span> TABLE<\/span> posts \r\nUSING<\/span> FTS5(title, body<\/span>);\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
INSERT<\/span> INTO<\/span> posts(title,body<\/span>)\r\nVALUES<\/span>('Learn SQlite FTS5'<\/span>,'This tutorial teaches you how to perform full-text search in SQLite using FTS5'<\/span>),\r\n('Advanced SQlite Full-text Search'<\/span>,'Show you some advanced techniques in SQLite full-text searching'<\/span>),\r\n('SQLite Tutorial'<\/span>,'Help you learn SQLite quickly and effectively'<\/span>);\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SELECT<\/span> * FROM<\/span> posts;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
Querying data using full-text search<\/h2>\r\n\r\n\r\n\r\n
You can execute a full-text query against an FTS5 table using one of these three ways.<\/p>\r\n\r\n\r\n\r\n
First, use a MATCH<\/code> operator in the WHERE<\/a> clause of the SELECT<\/a> statement. For example, to get all rows that have the term fts5<\/code>, you use the following query:<\/p>\r\n\r\n\r\n
SELECT<\/span> * \r\nFROM<\/span> posts \r\nWHERE<\/span> posts MATCH<\/span> 'fts5'<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
Second, use an equal (=<\/code>) operator in the WHERE<\/code> clause of the SELECT<\/code> statement. The following statement returns the same result as the statement above:<\/p>\r\n\r\n\r\n
SELECT<\/span> * \r\nFROM<\/span> posts \r\nWHERE<\/span> posts = 'fts5'<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Third, use a tabled-value function syntax. In this way, you use the search term as the first table argument:<\/p>\r\n\r\n\r\n
SELECT<\/span> * \r\nFROM<\/span> posts('fts5'<\/span>);\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
By default, FTS5 is case-independent. It treats the terms fts5<\/code>FTS5<\/code> and Fts5<\/code> the same.<\/p>\r\n\r\n\r\n\r\n
SELECT<\/span> * \r\nFROM<\/span> posts \r\nWHERE<\/span> posts MATCH<\/span> 'text'<\/span> \r\nORDER<\/span> BY<\/span> rank<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
Using full-text query syntax<\/h2>\r\n\r\n\r\n\r\n
A full-text search query is made up of phrases, where each phrase is an ordered list of one or more tokens. You can use the “+” operator to concatenate two phrases as the following example:<\/p>\r\n\r\n\r\n
\"learn SQLite\"\r\n\"learn + SQLite\"\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
FTS5 determines whether a document matches a phrase if the document contains at least one subsequence of tokens that match the sequence of tokens used to construct the phrase.<\/p>\r\n\r\n\r\n\r\n
The following query returns all documents that match the search term Learn SQLite<\/code>:<\/p>\r\n\r\n\r\n
SELECT<\/span> * \r\nFROM<\/span> posts \r\nWHERE<\/span> posts MATCH<\/span> 'learn SQLite'<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
Prefix searches<\/h3>\r\n\r\n\r\n\r\n
You can use the asterisk (*) as a prefix token. When a phrase contains the asterisk (*), it will match any document that contains the token that begins with the phrase. For example, search* matches with search, searching, searches, etc. See the following example:<\/p>\r\n\r\n\r\n
SELECT<\/span> * \r\nFROM<\/span> posts\r\nWHERE<\/span> posts = 'search*'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
Boolean operators<\/h3>\r\n\r\n\r\n\r\n
You can use the Boolean operator e.g., NOT<\/code>, OR<\/code>, or AND<\/code> to combine queries.<\/p>\r\n\r\n\r\n\r\n
  • q1 AND q2: matches if both q1 and q2 queries match.<\/li>
  • q1 OR q2: matches if either query q1 or q2 matches.<\/li>
  • q1 NOT q2: matches if query q1 matches and q2 doesn’t match.<\/li><\/ul>\r\n\r\n\r\n\r\n
    For example, to get the documents that match the learn<\/code> phrase but doesn’t match the FTS5<\/code> phrase, you use the NOT<\/code> operator as follows:<\/p>\r\n\r\n\r\n
    SELECT<\/span> * \r\nFROM<\/span> posts \r\nWHERE<\/span> posts MATCH<\/span> 'learn NOT text'<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    To search for documents that match either phrase learn<\/code> or text<\/code>, you use the OR<\/code> operator as the following example:<\/p>\r\n\r\n\r\n
    SELECT<\/span> * \r\nFROM<\/span> posts \r\nWHERE<\/span> posts MATCH<\/span> 'learn OR text'<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    To find the documents that match both SQLite and searching, you use the AND<\/code> operator as shown below:<\/p>\r\n\r\n\r\n
    SELECT<\/span> * \r\nFROM<\/span> posts \r\nWHERE<\/span> posts MATCH<\/span> 'sqlite AND searching'<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    To change the operator precedence, you use parenthesis to group expressions. For example:<\/p>\r\n\r\n\r\n
    SELECT<\/span> * \r\nFROM<\/span> posts \r\nWHERE<\/span> posts MATCH<\/span> 'search AND sqlite OR help'<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    The statement returns documents that match search<\/code> and sqlite<\/code> or help<\/code>. To find the documents that match search<\/code> and either sqlite<\/code> or help<\/code>, you use parenthesis as follows:<\/p>\r\n\r\n\r\n
    SELECT<\/span> * \r\nFROM<\/span> posts \r\nWHERE<\/span> posts MATCH<\/span> 'search AND (sqlite OR help)'<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    Built-in auxiliary functions<\/h2>\r\n\r\n\r\n\r\n
    SQLite provides three built-in auxiliary functions that can be used within full-text queries on the FTS5 table.<\/p>\r\n\r\n\r\n\r\n
    • The bm25()<\/code> returns a value that represents the accuracy of the current match, the lower value means a better match.<\/li>
    • The highlight()<\/code> auxiliary function returns a copy of the text with search terms surrounded by a specified markup e.g.,<b>search term <\/b><\/li>
    • The snippet()<\/code> selects a short fragment of text in order to maximize the number of search terms it contains.<\/li><\/ul>\r\n\r\n\r\n\r\n
      For example, the following query uses the highlight() function to decorate the search terms using the <b>tag<\/b>: <\/b><\/p>\r\n\r\n\r\n
      SELECT<\/span> highlight(posts,0<\/span>, '<b>'<\/span>, '<\/b>'<\/span>) title, \r\n       highlight(posts,1<\/span>, '<b>'<\/span>, '<\/b>'<\/span>) body<\/span>\r\nFROM<\/span> posts \r\nWHERE<\/span> posts MATCH<\/span> 'SQLite'<\/span>\r\nORDER<\/span> BY<\/span> rank<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
      \"SQLite<\/figure>\r\n\r\n\r\n\r\n
      In this tutorial, you have learned how to use SQLite full-text search features via FTS5 virtual table module.<\/p>\r\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
To sort the search results from the most to least relevant, you use the ORDER BY<\/a> clause as follows:<\/p>\r\n\r\n\r\n
And query <\/a>data against it:<\/p>\r\n\r\n\r\n
Similar to a normal table, you can insert data<\/a> into the posts<\/code> table as follows:<\/p>\r\n\r\n\r\n