FREETEXT

SQL Server

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.