SQL Reference - FREETEXT

FREETEXT

FREETEXT is a predicate used in SQL that enables free-text searching against character-based data. It uses full-text indexing to search for values that match or are near matches to the specified text. It's often used to broaden the search criteria in a query to include synonyms or words related to the specified text. It also supports language specific searches, identifying inflectional forms of a word for a specific language.

FREETEXT( { column | * } , ‘freetext_string’ [ , LANGUAGE language_term ] )

{ column | }: This represents the column or the list of columns in a table in which the search will be performed. ’*’ can be used to specify that the search should include all columns of the table.
‘freetext_string’: This defines the string for which the search will be made. SQL Server will return the records where this string exists in the free-text index.
language language_term: This is an optional parameter. It represents the language in which the free-text_string is given. If the LANGUAGE language_term is not specified, the column’s default full-text language is used.

Example

CREATE TABLE Books
(
    Book_Id INT PRIMARY KEY,
    Title NVARCHAR(50),
    Description NVARCHAR(MAX)
)

INSERT INTO Books
VALUES
     (1, 'SQL Server Essentials', 'This book contains foundational information on SQL Server'),
     (2, 'Exploring Python', 'A great book for beginners looking to delve into Python programming')

SELECT * FROM Books
WHERE FREETEXT(Description,'SQL Server')

Output

| Book_Id |               Title               |                           Description                           |
|---------|-----------------------------------|-----------------------------------------------------------------|
| 1       | SQL Server Essentials             | This book contains foundational information on SQL Server       |

Explanation

In the example, we created a Books table, inserted two records. The FREETEXT function is used in the SELECT query to find all books whose description contains ‘SQL Server’. FREETEXT handles inflectional forms of a word. So, it found the record with ‘SQL Server’ in the Description.