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 - geeksforgeeksExample 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:
- Match Operator
- "=" Operator
- 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 FTS5Example 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 syntaxExplanation: 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 25Explanation: 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:-
- Table name
- Column Index. (left-right based indexing starting from 0)
- Symbol/text to insert before each matched data
- 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:
highlightExplanation: 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:
SnippetExplanation: 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 FTS5Explanation: 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 OperatorExplanation: 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 OperatorExplanation: 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 OperatorExplanation: 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 columnExplanation: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 ClauseConclusion
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.
Similar Reads
FULL OUTER JOIN in SQLite
In the area of data querying and manipulation, the ability to combine information from different sources is important. SQLite, a popular embedded database engine, offers a range of join operations to fast process. One such operation FULL OUTER JOIN is particularly powerful as it allows us to merge d
4 min read
MongoDB Text Search
MongoDB Text Search is a powerful feature designed to find specified text within string content in MongoDB collections. This capability is essential for applications that require efficient and effective searching capabilities. Text indexes in MongoDB should be strings or arrays of string elements an
7 min read
How to Search Text in a SQL Server Stored Procedure
A stored procedure is a compiled SQL code that is saved in the database and can be reusable by calling this from a Client Application or another stored procedure. When there are tens and hundreds of stored procedures and if a programmer wants to find out if there is a stored procedure for some speci
6 min read
SQL FULL JOIN
In SQL, the FULL JOIN (or FULL OUTER JOIN) is a powerful technique used to combine records from two or more tables. Unlike an INNER JOIN, which only returns rows where there are matches in both tables, a FULL JOIN retrieves all rows from both tables, filling in NULL values where matches do not exist
4 min read
SQL Server | STUFF() Function
The STUFF() function in SQL Server is a powerful string manipulation tool used to delete a specified length of characters from a string and insert another set of characters at a given starting position. This function becomes particularly useful in scenarios where complex string operations are requir
2 min read
The Best Twitter Search Tricks
Twitter is an excellent tool for real-time updates. With the help of this article, every person who has encountered this platform for searching can keep up-to-date with the most important information regarding tweets, users, and more. I simply would like to point out that for anyone using Twitter in
5 min read
Test Case For Search Functionality
The search functionality of the software should allow users to search for specific content within the software. The search results should be displayed in a manner that is easy to understand and navigate. The article focuses on discussing test cases for the search functionality:Test Case For Search F
3 min read
SQLite TEXT vs VARCHAR
In SQLite, TEXT and VARCHAR are the fundamental building blocks used for storing textual information. TEXT is an advantage in that it can store as many characters as necessary without imposing restrictions on the maximum number and VARCHAR capacity to specify a maximum string length. It also provide
4 min read
SQLite GLOB Clause
SQLite is a server-less database engine and it is written in C programming language. The main moto for developing SQLite is to escape from using complex database engines like MYSQL etc. It has become one of the most popular database engines as we use it in Television, Mobile Phones, web browsers, an
5 min read
MySQL SELECT Statement
The MySQL SELECT statement is essential for fetching data from tables. It retrieves information and stores it in a result table, often referred to as a result set. Widely used in MySQL, SELECT is a fundamental command for querying databases. This article covers the basics of SELECT syntax and explor
4 min read