FREETEXTTABLE

SQL Server

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).