CONTAINSTABLE (Transact-SQL)
Returns a table of zero, one, or more rows for those columns containing character-based data types for 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. CONTAINSTABLE can only be referenced in the FROM clause of a SELECT statement as if it were a regular table name.
Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.
CONTAINSTABLE (table , { column_name | (column_list ) | * } ,' < contains_search_condition > '
[ , LANGUAGE language_term]
[ ,top_n_by_rank ]
)
< contains_search_condition > ::=
{ < simple_term >
| < prefix_term >
| < generation_term >
| < proximity_term >
| < weighted_term >
}
| { ( < contains_search_condition > )
{ { AND | & } | { AND NOT | &! } | { OR | | } }
< contains_search_condition > [ ...n ]
}
< simple_term > ::=
word | "phrase "
< prefix term > ::=
{ "word *" | "phrase *" }
< generation_term > ::=
FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] )
< proximity_term > ::=
{ < simple_term > | < prefix_term > }
{ { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ]
< weighted_term > ::=
ISABOUT
( { {
< simple_term >
| < prefix_term >
| < generation_term >
| < proximity_term >
}
[ WEIGHT (weight_value ) ]
} [ ,...n ]
)
Full-text predicates and functions work on a single table, which is implied in the FROM predicate. To search on multiple tables, use a joined table in your FROM clause to search on a result set that is the product of two or more tables.
The table returned has a column named KEY that contains full-text key values. Each full-text indexed table has a column whose values are guaranteed to be unique, and the values returned in the KEY column are the full-text key values of the rows that match the selection criteria specified in the contains search condition. The TableFulltextKeyColumn property, obtained from the OBJECTPROPERTYEX function, provides the identity of this unique key column. To obtain the ID of the column associated with the full-text key of the full-text index, use sys.fulltext_indexes. For more information, see sys.fulltext_indexes (Transact-SQL).
To obtain the rows you want from the original table, specify a join with the CONTAINSTABLE rows. The typical form of the FROM clause for a SELECT statement using CONTAINSTABLE is:
SELECT select_list FROM table AS FT_TBL INNER JOIN CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
The table produced by CONTAINSTABLE includes a column named RANK. The RANK column is a value (from 0 through 1000) for each row indicating how well a row matched the selection criteria. This rank value is typically used in one of these ways in the SELECT statement:
In the ORDER BY clause to return the highest-ranking rows as the first rows in the table.
In the select list to see the rank value assigned to each row.
CONTAINSTABLE is not recognized as a keyword if the compatibility level is less than 70. For more information, see sp_dbcmptlevel (Transact-SQL).
A. Returning rank values using CONTAINSTABLE
The following example searches for all product names containing the words breads, fish, or beers, and different weightings are given to each word. For each returned row matching this search criteria, the relative closeness (ranking value) of the match is shown. In addition, the highest ranking rows are returned first.
USE Northwind;
GO
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL
INNER JOIN CONTAINSTABLE(Categories, Description,
'ISABOUT (breads weight (.8),
fish weight (.4), beers weight (.2) )' ) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
GO
B. Returning rank values greater than specified value using CONTAINSTABLE
The following example returns the description and category name of all food categories for which the Description column contains the words "sweet and savory" near either the word sauces or the word candies. All rows that have a category name Seafood are disregarded. Only rows with a rank value of 2 or higher are returned.
USE Northwind;
GO
SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK
FROM Categories AS FT_TBL
INNER JOIN CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
AND FT_TBL.CategoryName <> 'Seafood'
ORDER BY KEY_TBL.RANK DESC;
GO
C. Returning top 10 ranked results using CONTAINSTABLE and top_n_by_rank
The following example returns the description and category name of the top 10 food categories where the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies".
USE Northwind;
SELECT FT_TBL.Description, FT_TBL.CategoryName , KEY_TBL.RANK
FROM Categories AS FT_TBL
INNER JOIN CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)', 10)
AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
GO
D. Specifying the LANGUAGE argument
The following example shows using the LANGUAGE argument.
USE Northwind;
SELECT FT_TBL.Description , FT_TBL.CategoryName , KEY_TBL.RANK
FROM dbo.Categories AS FT_TBL
INNER JOIN CONTAINSTABLE (dbo.Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)',LANGUAGE N'English', 10)
AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY];
 Note |
|---|
The LANGUAGE language_term argumentis not required for using top_n_by_rank. |
