SQLite Full-text Search

SQLite is serverless architecture which means it does not requires any type of server to be installed on the system. It is also a relational database management system which stores the data into tabular or relational manner. It is used in embedded software and useful for the small and medium scale applications.

In this article we will learn about the Full-Text Search in detail along with their implementations and examples and so on. After reading this article you will have decent knowledge about the Full-Text Search and you will able to perform various operations.

Full-text Search in SQLite

In SQLite, Full-text-Search or FTS5 is an extension used in database management systems to efficiently search and retrieve information or data from a large dataset. Other searching techniques might consume more time than the FTS searching techniques.

While core SQLite features are small in number, SQLite provides a vast number of extensions that extend its features to fulfill the requirement. Full-text search extensions or FTS5 is an extension pre-installed in SQLite.

We usually work with a virtual table rather than an actual table in the case of FTS5 extensions. We commonly copy the data from the actual table into the virtual table. All the changes made in the actual table are also made in the virtual table.

Syntax:

SELECT * 
FROM table_name
WHERE table_name MATCH 'exampleSearch';

Note: This is one of the way to implement FTS5. We will see some more ways to implement FTS5 in further points discussed in this article.

Examples of Full Text Search

As previously stated in the article, we need to define a virtual table in order to implement FTS5 in our table. Lets create a virtual table with dummy data.

Note :- While creating a virtual table, we cannot add data types, constraints or PRIMARY KEY. If we do so, it will throw us an error

We have created a table called geeksforgeeks which consists of name, score, courses as Columns.

Query:

CREATE VIRTUAL TABLE geeksforgeeks
USING FTS5(name,score,courses);

Lets add data to the table and display it.

Query:

INSERT INTO geeksforgeeks(name, score, courses)
VALUES
  ('Vishu', 100, 'Python self paced'),
  ('Sumit', 95, 'Java self paced'),
  ('Neeraj', 90, 'Javascript'),
  ('Aayush', 85, 'Cpp'),
  ('Harsh', 80, 'Python Data Structures self paced');

SELECT * from geeksforgeeks;

Output:

Table – geeksforgeeks

Example 1: Querying Data Using Full-Text Search or FTS5.

Lets search all the columns in the table where text match matches with “self paced“.

We can implement this through three ways, Which are:

1. Match Operator
2. “=” Operator
3. Table-valued syntax

1. Match Operator

Query:

SELECT * 
FROM geeksforgeeks 
WHERE geeksforgeeks MATCH 'self paced';

2. “=” Operator

Query:

SELECT * 
FROM geeksforgeeks 
WHERE geeksforgeeks = 'self paced';

3. Table-valued Syntax

Query:

SELECT * 
FROM geeksforgeeks('self paced');

Output of the all above queries are same. It is shown below:

Querying data using FTS5

Example 2: Using Full-Text Query Syntax

In this technique, if FTS5 finds data that aligns with the sequence of tokens that matches with the user’s search than it will consider this data to be displayed.

For example:

Search String : “Python self paced “

It has three tokens : “Python”, “self”, “paced “.

If a string comes like : Python Data Structure self paced then it will also consider a match for the user search.

Lets write a query to understand it better.

Query:

SELECT * 
FROM geeksforgeeks('Python self paced');

Output:

Using full-text query syntax

Explanation: As explained above, it consider “Python Data Structure self paced” a worth match. As it has all the tokens with correct order.

Example 3: Built-in Auxiliary Functions

1. bm25()

Best Matching 25 or bm25 is ranking technique which assigns a relevance score to data based on their relevance to the user search.

Query:

SELECT *, bm25(geeksforgeeks,0,0,1) as bm25
FROM geeksforgeeks
WHERE geeksforgeeks MATCH 'Python self paced';

Output:

Best Matching 25

Explanation: This function accepts table name along with weight of the columns respectively.

2. highlight()

This generate results contains highlighted occurrence of the user searched value. We can use bold tags, italic tags, symbols etc.

This function takes 4 values as parameters:-

1. Table name
2. Column Index. (left-right based indexing starting from 0)
3. Symbol/text to insert before each matched data
4. Symbol/text to insert after each matched data

Lets see an example :-

Query:

SELECT name, highlight(geeksforgeeks, 2, '<b>', '</b>') courses
FROM geeksforgeeks
WHERE geeksforgeeks MATCH 'Python';

Output:

highlight

Explanation: We can clearly see our searched text “Python” is shown in the result enclosed with bold tags.

3. Snippet()

It is similar to highlight() but the sole difference is that it trims the search depending on the varying length of data. Length of displayed result should lie between 0 to 64.

Query:

SELECT  name, snippet(geeksforgeeks, 2,'<b>', '</b>',  '',  3) courses
FROM geeksforgeeks
WHERE geeksforgeeks MATCH 'Python';

Output:

Snippet

Explanation: We can clearly observe in the second row of courses column, it trims the data to 3 tokens.

Example 4: Prefix Searches in FTS5

In this example we will cover prefix searching technique in FTS5. We can use asterisk (*) as a prefix token. We will use this symbol at the end of the search and it will result all the values that matches with the token that begins with the user search.

For example if user search : Java

Java* matches with Java, JavaScript, Java-Courses etc.

Query:

SELECT * 
FROM geeksforgeeks 
WHERE geeksforgeeks MATCH 'Java*';

Output:

Prefix searches in FTS5

Explanation: In the above image we can clearly observe that Java* results two rows one with Java as starting phrase another Javascript as starting phrase.

Example 5: FTS5 with Boolean Operators.

We can use Boolean operators to make a search in FTS5. In the below example we will covered, how to make searches with OR, AND, NOT.

1. FTS5 with OR Operator

Lets search for all records where courses taken are either related to Python or Java.

Query:

SELECT * 
FROM geeksforgeeks 
WHERE geeksforgeeks MATCH 'Python OR Java';

Output:

FTS5 with OR Operator

Explanation: We can clearly observe all the records are displayed which has either Python or Java.

2. FTS5 with AND Operator

Lets search for all records where courses taken are related to Python and Data Structure.

Query:

SELECT * 
FROM geeksforgeeks 
WHERE geeksforgeeks MATCH 'Python AND Data';

Output:

FTS5 with AND Operator

Explanation: We can clearly see only one record is displayed and this is the only record that fulfils the condition.

3. FTS5 with NOT Operator

Lets search for all records where courses taken are either related to Python but not Java.

Query:

SELECT * 
FROM geeksforgeeks 
WHERE geeksforgeeks MATCH 'Python NOT Java';

Output:

FTS5 with NOT Operator

Explanation: We can clearly observe all the records are displayed where search matches with Python and it do not contain Java.

Example 6: FTS5 Search from Single Column.

In FTS5, usually search is done from all the columns in the virtual table. To limit this search to only some limited columns we can write some more detailed query. Lets see the query from below.

Query:

SELECT * 
FROM geeksforgeeks 
WHERE geeksforgeeks MATCH 'courses: self paced';

Output:

FTS5 Search from Single column

Explanation:We can observe all the displayed records have self paced in courses column. In the query we have specified to search from only course column : MATCH ‘courses: self paced’.

Example 7: FTS5 with Order by Clause

In this example, We will sort the displayed record. In FTS5, We can use either rank or bm25() to achieve this task. For this example, we are going to use rank. Although bm25() and rank has same application their is a slight difference between them discussed under “Built-in auxiliary functions”.

Query:

SELECT *
FROM geeksforgeeks
WHERE geeksforgeeks MATCH 'Python OR Java*'
ORDER BY rank;

Output:

FTS5 with Order by Clause

Conclusion

Full-text-Search or FTS5 is an extension in SQLite. While SQLite has limited parent features, it has vast number of extensions available. FTS5 is applied on virtual table rather than the actual table. All the data of actual table is copied in virtual table. Any changes made in virtual table must be done in actual table or vice versa. It is a pre installed extension. Therefore we need not to install this extension. We have covered all the aspects of FTS5 with detailed example along with their respective explanations.