CONTAINSTABLE (Transact-SQL)
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. CONTAINSTABLE is used in the FROM clause of a Transact-SQL SELECT statement and is referenced as if it were a regular table name. .It performs a SQL Server full-text search on full-text indexed columns containing character-based data types.
CONTAINSTABLE is useful for the same kinds of matches as the CONTAINS predicate and uses the same search conditions as CONTAINS.
Unlike CONTAINS, however, queries using CONTAINSTABLE return a relevance ranking value (RANK) and full-text key (KEY) for each row.
Note
|
|---|
|
For information about the forms of full-text searches that are supported by SQL Server, see Query with Full-Text Search. |
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>
| <generic_proximity_term>
| <custom_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 ] )
<generic_proximity_term> ::=
{ <simple_term> | <prefix_term> } { { { NEAR | ~ }
{ <simple_term> | <prefix_term> } } [ ...n ] }
<custom_proximity_term> ::=
NEAR (
{
{ <simple_term> | <prefix_term> } [ ,…n ]
|
( { <simple_term> | <prefix_term> } [ ,…n ] )
[, <maximum_distance> [, <match_order> ] ]
}
)
<maximum_distance> ::= { integer | MAX }
<match_order> ::= { TRUE | FALSE }
<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.
A. Returning rank values
The following example searches for all product names containing the words "frame," "wheel," or "tire," and different weights are given to each word. For each returned row matching these search criteria, the relative closeness (ranking value) of the match is shown. In addition, the highest ranking rows are returned first.
USE AdventureWorks2012
GO
SELECT FT_TBL.Name, KEY_TBL.RANK
FROM Production.Product AS FT_TBL
INNER JOIN CONTAINSTABLE(Production.Product, Name,
'ISABOUT (frame WEIGHT (.8),
wheel WEIGHT (.4), tire WEIGHT (.2) )' ) AS KEY_TBL
ON FT_TBL.ProductID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO
B. Returning rank values greater than a specified value
The following example uses NEAR to search for "bracket" and "reflector" close to each other in the Production.Document column of the AdventureWorks2008 database. Only rows with a rank value of 50 or higher are returned.
USE AdventureWorks2012 GO SELECT DocumentNode, Title, DocumentSummary FROM Production.Document AS DocTable INNER JOIN CONTAINSTABLE(Production.Document, Document, 'NEAR(bracket, reflector)' ) AS KEY_TBL ON DocTable.DocumentNode = KEY_TBL.[KEY] WHERE KEY_TBL.RANK > 50 ORDER BY KEY_TBL.RANK DESC GO
|
Note
|
|---|
|
If a full-text query does not specify an integer as the maximum distance, a document that contains only hits whose gap is greater than 100 logical terms will not meet the NEAR requirements, and its ranking will be 0. |
C. Returning top 5 ranked results using top_n_by_rank
The following example returns the description of the top 5 products where the Description column contains the word "aluminum" near either the word "light" or the word "lightweight".
USE AdventureWorks2012
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)',
5
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
GO
GO
D. Specifying the LANGUAGE argument
The following example shows using the LANGUAGE argument.
USE AdventureWorks2012
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
CONTAINSTABLE (Production.ProductDescription,
Description,
'(light NEAR aluminum) OR
(lightweight NEAR aluminum)',
LANGUAGE N'English',
5
) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
GO
|
Note
|
|---|
|
The LANGUAGE language_term argumentis not required for using top_n_by_rank. |
