SQL Reference - FREETEXTTABLE

FREETEXTTABLE

FREETEXTTABLE is a SQL Server function used in full-text search. It searches for values that match in meaning, but not necessarily in exact form, to the search criteria. The function returns a table of zero, one, or more rows for all the matches found in full-text indexed columns of the specified table. The returned table includes a "KEY" column containing the unique key value for each row and a "RANK" column that measures how well the search criteria matches the full-text indexed data.

FREETEXTTABLE( table , column , free_text_search , LANGUAGE language_term , top_n_by_rank )

table: This parameter specifies the name of the table to be searched. The table should be a full-text indexed table.
column: Defines the name of the column to be searched. This should be a column from the specified table which also has to be full-text indexed.
free_text_search: This parameter is used to specify the free text to look for in the column(s). It takes the form of a simple word, phrase or a complex word form that is used for matching against the full-text indexed columns.
language language_term: This parameter is used to specify the language of the search argument. By using this parameter, the Full-Text Engine can identify the language to use for word breaking, stemming, and the thesaurus.
top_n_by_rank: This optional parameter limits the output of FREETEXTTABLE function. It specifies that the returned table will contain only the top n rows. The rows are sorted by their relevance ranks, from high to low.

Example

SELECT *
FROM FREETEXTTABLE(ProductCatalog, Description, 'leather wallet')

Output

| Key  | Rank  |
|------|-------|
| 5    | 16    |
| 7    | 20    |
| 3    | 36    |

Explanation

The FREETEXTTABLE function is used in SQL Server to perform full-text searches on the text data in full-text indexed columns. It returns a table of zero, one, or more rows for those documents containing key phrases.

In the given example, FREETEXTTABLE was used to search the ‘ProductCatalog’ table’s ‘Description’ column for the phrase ‘leather wallet’. The function returned a table containing the keys of the rows where a match was found, and a rank indicating the relevance of the row to the search phrase (higher rank indicates higher relevance).