SQLite Full Text Search

In the Database, the FTS5 virtual table component is used to do comprehensive searches. A virtual table is an SQLite customizable plugin. A virtual table is similar to a regular table. The distinction between a virtual table and a regular table would be where the data comes from. SQLite queries the database schema to obtain data while processing a regular table. Whenever you visit a virtual table, SQLite, on the other hand, invokes the program code to retrieve the contents. Certain activities, such as obtaining data from numerous datasets, can be handled by customized code with specific logic.

Let’s get started with this article by starting the SQLite database— C-library of SQL on your system. For this, try out the “sqlite3” keyword as an instruction on the shell and you are good to go. The terminal for the SQLite database has been launched. We have tried the .tables instruction on its query area to list down all the already existing tables of the database. Unfortunately, we have no tables in the database right now. Thus, we need to create a table to perform a full-text search.

Let’s create a new table TEST in the SQLite database using the CREATE TABLE query of the database. This table will contain the ID column of integer type and the TITLE of text type. Both columns must not be null as per the CREATE TABLE query.

CREATE TABLE TEST(ID INT PRIMARY KEY, TITLE TEXT NOT NULL);

After the creation of a TEST table, we need to put some values in it. We have been adding the values for both columns ID and TITLE as shown. After that, we tried the “SELECT” instruction with the “*” character to fetch all the newly inserted records of a TEST table.

INSERT INTO TEST(ID, TITLE) VALUES (1, 'Wonders');
INSERT INTO TEST(ID, TITLE) VALUES (2, 'Maleficient');
INSERT INTO TEST(ID, TITLE) VALUES (3, 'Doctor Strange');
INSERT INTO TEST(ID, TITLE) VALUES (4, 'Lucky');
INSERT INTO TEST(ID, TITLE) VALUES (5, 'Rush');

SELECT * FROM Test;

Let’s search for the full-text search from the TITLE column of the TEST table where the record matches “fts5”.

SELECT * FROM Test WHERE TITLE MATCH 'fts5';

Let’s move forward to perform a full-text search on the table. The full-text search can also be used on the Virtual tables. Thus, we need to create a new virtual table. For this, we have been using the CREATE VIRTUAL TABLE command along with the name of a table, “DATA”. Followed by the keyword USING FTS5 and the column names in the brackets. These columns don’t contain any data types while the creation of a table as FTS5 has been applied.

CREATE VIRTUAL TABLE DATA USING FTS5(TITLE, INFO);

After creating the Virtual table, we need to insert records in the two columns “title”, and “info” of a table “DATA”. So, we have added a total of 5 records within both columns “title” and “info” of a DATA table. You can see that the columns contain text values containing small and large amounts of data within.

INSERT INTO DATA(TITLE, INFO) VALUES ('Wonders', 'This film is about 7 friends stuck in the land of wonders.

Earned a total of 230 million dollars);

INSERT INTO DATA(TITLE, INFO) VALUES ('Maleficient', 'This film is about fantasy, magic, and romance famous about youngsters. Earned total of 300 million dollars);

INSERT INTO DATA(TITLE, INFO) VALUES ('Inception', 'This film IS about changing your future BY doing TO dreams. Earned a total OF 500 million dollars);

INSERT INTO DATA(TITLE, INFO) VALUES ('2020', 'This movie portrays that the world will meet its end in 2020 after hitting a huge tsunami. Earned a total of 600 million dollars);

INSERT INTO DATA(TITLE, INFO) VALUES ('Doctor Strange', 'It's about magic and its use of it to heal. Earned a total of 800 million dollars);

After adding the records, we have been fetching all its data using the SELECT instruction with the ‘*’ character in the query followed by the name of a table DATA. It displayed all 5 records.

Let’s search for the full text in the columns of a table DATA. For this, we have been using the SELECT instruction with the matching string ‘fts5’ in the query. The WHERE clause contains the condition that the DATA table must match the string ‘fts5’ as per the MATCH keyword. You can also replace MATCH with the “=” operator. It turns out there is no ‘fts5’ string in the table.

SELECT * FROM DATA WHERE DATA MATCH 'fts5';
SELECT * FROM DATA WHERE DATA = 'fts5';

So, we have been adding a new record in the table that contains the pattern ‘fts5’ in its content. We have tried the INSERT INTO instruction once again so far. After displaying the data of a table DATA once again, we have seen that the newly inserted record has also been displayed.

Let’s apply the full-text search on the DATA table using the SELECT instruction followed by the keyword MATCH and the string pattern ‘fts5’ to display the only records containing the specific pattern. It displayed a single record on our screen. The same query has been executed with the “=” operator instead of the MATCH keyword and it displayed the very same output.

SELECT * FROM DATA WHERE DATA MATCH 'fts5';
SELECT * FROM DATA WHERE DATA = 'fts5';
SELECT * FROM DATA('fts5');

Let’s display the records of the table in the most to least relevant order of the inserted records. For this, we will be using the “rank” option in the query. So, we have been using full-text search to search for the text ‘wonders’ from the table DATA via the MATCH keyword and the ORDER BY clauses following the “rank” option. It displayed a single record from the table.

SELECT * FROM DATA WHERE DATA MATCH 'Wonders' ORDER BY rank;

Another way to perform a full-text search is using the pattern within the brackets after the table name in the SELECT instruction.

SELECT * FROM DATA('magic');

You can also use the Full-text search to search for the prefix pattern values from the table contents. To use prefixes, you need to add the first two to three characters followed by the “*” character. This prefix value can be used in the brackets of the SELECT instruction. Now, the use of “ro*” is displaying the single record for the Maleficent movie whose description information contains the prefix “ro” for “romance”.

SELECT * FROM DATA('ro*');

You can also restrict the search of one pattern to the other using the NOT operator. As we have been using the magic NOT text, thus it displayed two records for the magic pattern.

SELECT * FROM DATA WHERE DATA MATCH 'magic NOT text';

Conclusion

This guide is all about the use of Full-text search in the SQLite database used in Ubuntu 20.04. Using FTS5, we can search for a specific pattern along with some conditions. This detailed article will help you to search text in SQLite.

SQLite Full Text Search

Conclusion

About the author

Omar Farooq