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 w3wiki which consists of name, score, courses as Columns.
Query:
CREATE VIRTUAL TABLE w3wiki
USING FTS5(name,score,courses);
Lets add data to the table and display it.
Query:
INSERT INTO w3wiki(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 w3wiki;
Output:
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:
- Match Operator
- “=” Operator
- Table-valued syntax
1. Match Operator
Query:
SELECT *
FROM w3wiki
WHERE w3wiki MATCH 'self paced';
2. “=” Operator
Query:
SELECT *
FROM w3wiki
WHERE w3wiki = 'self paced';
3. Table-valued Syntax
Query:
SELECT *
FROM w3wiki('self paced');
Output of the all above queries are same. It is shown below:
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 w3wiki('Python self paced');
Output:
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(w3wiki,0,0,1) as bm25
FROM w3wiki
WHERE w3wiki MATCH 'Python self paced';
Output:
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:-
- 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(w3wiki, 2, '<b>', '</b>') courses
FROM w3wiki
WHERE w3wiki MATCH 'Python';
Output:
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(w3wiki, 2,'<b>', '</b>', '', 3) courses
FROM w3wiki
WHERE w3wiki MATCH 'Python';
Output:
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 w3wiki
WHERE w3wiki MATCH 'Java*';
Output:
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 w3wiki
WHERE w3wiki MATCH 'Python OR Java';
Output:
2. FTS5 with AND Operator
Lets search for all records where courses taken are related to Python and Data Structure.
Query:
SELECT *
FROM w3wiki
WHERE w3wiki MATCH 'Python AND Data';
Output:
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 w3wiki
WHERE w3wiki MATCH 'Python NOT Java';
Output:
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 w3wiki
WHERE w3wiki MATCH 'courses: self paced';
Output:
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 w3wiki
WHERE w3wiki MATCH 'Python OR Java*'
ORDER BY rank;
Output:
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.