SQL Reference - CONTAINSTABLE

CONTAINSTABLE

CONTAINSTABLE is a Transact-SQL function in Microsoft SQL Server that returns a table of zero, one, or more rows for those columns containing precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. It's used primarily for implementing full-text search capabilities.

CONTAINSTABLE( table, { column | * } , contains_search_condition [ , LANGUAGE language_term ] [ , top_n_by_rank ] )

table: This is the name of the table to be searched.
{ column | }: This holds the specific column name(s) to be searched or a * can be used for searching all columns.
contains_search_condition: A variable that defines the condition to be met with the CONTAINS predicate. It includes keywords or phrases to search for in the specified columns.
language language_term: An optional clause that specifies the language of the word. If not given, the default language of the column is used.
top_n_by_rank: Another optional argument, it indicates the number of rows to be returned, sorted by their rank in descending order. If absent, the query returns all qualifying rows.

Example

SELECT * FROM CONTAINSTABLE(MyTable, ColumnName, 'expression')

Output

Key     | Rank
--------|------
1       | 72
2       | 48
3       | 36

Explanation

CONTAINSTABLE, in this example, scans MyTable for the text ‘expression’ in ColumnName. It returns the key of matching rows, along with a “rank” column that estimates how relevant each row is to the given expression. The higher the rank, the more relevant the row’s data is to the expression.