SQL Reference - FULLTEXT

Example

CREATE TABLE Article (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
);

INSERT INTO Article (title, content)
VALUES('Sample Article', 'This is a sample article for testing fulltext search.');

SELECT * FROM Article
WHERE MATCH (title,content)
AGAINST ('sample' IN NATURAL LANGUAGE MODE);

Output

+----+----------------+----------------------------------------+
| id |     title      |                content                 |
+----+----------------+----------------------------------------+
|  1 | Sample Article | This is a sample article for testing...|
+----+----------------+----------------------------------------+

Explanation

In the above example, a FULLTEXT index is built on the ‘title’ and ‘content’ columns of the ‘Article’ table. As a result, the use of FULLTEXT allows you to perform text search queries on these fields. The MATCH() function is used with AGAINST() to search for rows that contain the word ‘sample’ in either the title or the content. The ‘IN NATURAL LANGUAGE MODE’ option specifies that the search is to be performed using the natural language (non-boolean) full-text search mode. In the output, the query returns the row where the word ‘sample’ appears either in the ‘title’ or ‘content’.

Example

CREATE TABLE Blogs(
    id INT PRIMARY KEY,
    contents NVARCHAR(1000)
);

INSERT INTO Blogs
VALUES
(1, 'SQL is a standard language for managing data held in a relational database management system (RDBMS).'),
(2, 'SQL stands for Structured Query Language.'),
(3, 'SQL commands are used to perform tasks such as update data on a database, or retrieve data from a database.');

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Blogs(contents)
KEY INDEX PK_Blogs
WITH STOPLIST = SYSTEM;

SELECT * FROM Blogs
WHERE CONTAINS(contents, 'database');

Output

id  | contents
--- | ---
1   | SQL is a standard language for managing data held in a relational database management system (RDBMS).
3   | SQL commands are used to perform tasks such as update data on a database, or retrieve data from a database.

Explanation

In the provided SQL Server example, a table named ‘Blogs’ is created with ‘id’ as the primary key and ‘contents’ as the body of each record. Three records are inserted into this table.

A full-text catalog ‘ftCatalog’ is created with a full-text index on the ‘contents’ column of the table. This enables full-text search on the ‘contents’ column.

The CONTAINS keyword is used in the SELECT query to search within the ‘contents’ column for the term ‘database’. The output of this query displays the records which contain ‘database’ in their ‘contents’ field.

Example

CREATE VIRTUAL TABLE books USING FTS4(title, author);
INSERT INTO books (title, author) VALUES('The Great Gatsby', 'F. Scott Fitzgerald');
INSERT INTO books (title, author) VALUES('To Kill a Mockingbird', 'Harper Lee');
SELECT * FROM books WHERE books MATCH 'F.*';

Output

title                  | author
The Great Gatsby      | F. Scott Fitzgerald

Explanation

In this example, a full-text search is used in SQLite to find the rows in which the author’s name starts with ‘F’. The MATCH keyword is used with the books table to perform the full text search.

An FTS4 table named ‘books’ is created with ‘title’ and ‘author’ as columns. Two rows are inserted into the ‘books’ table. The select query finds the rows where the author’s name starts with ‘F’. The result is the row with the ‘The Great Gatsby’ by ‘F. Scott Fitzgerald’.