CONTAINSTABLE (T-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 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) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.

Syntax

CONTAINSTABLE (table, {column | *}, '<contains_search_condition>')

<contains_search_condition> ::=
        {
        | <generation_term>
        | <prefix_term>
        | <proximity_term>
        | <simple_term>
        | <weighted_term>
        }
        | { (<contains_search_condition>)
        {AND | AND NOT | OR} <contains_search_condition> [...n]
        }

<weighted_term> ::=
    ISABOUT
        (    {    {
                    <generation_term>
                    | <prefix_term>
                    | <proximity_term>
                    | <simple_term>
                }
                [WEIGHT (weight_value)]
            }  [,...n]
        )

<generation_term> ::=
    FORMSOF (INFLECTIONAL, <simple_term> [,...n] )

<prefix term> ::=
    { "word * " | "phrase * " }

<proximity_term> ::=
    {<simple_term> | <prefix_term>}
    {    {NEAR | ~} {<simple_term> | <prefix_term>} } [...n]

<simple_term> ::=
    word | " phrase "

Arguments

table

Is the name of the table that has been marked for full-text querying. table can be a one-part database object name or a multipart database object name. For more information, see Transact-SQL Syntax Conventions.

column

Is the name of the column to search, which resides in table. Columns of the character string data types are valid full-text searching columns.

*

Specifies that all columns in the table that have been registered for full-text searching should be used to search for the given contains search condition(s).

<contains_search_condition>

Specifies some text to search for in column. Variables cannot be used for the search condition. For more information, see CONTAINS.

Remarks

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 OBJECTPROPERTY function, provides the identity for this unique key column. 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 (between 0 and 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.
• In the WHERE clause to filter out rows with low rank values.

CONTAINSTABLE is not recognized as a keyword if the compatibility level is less than 70. For more information, see sp_dbcmptlevel.

Permissions

Execute permissions are available only by users with the appropriate SELECT privileges on the table or the referenced table’s columns.

Examples

A. Return rank values using CONTAINSTABLE

This 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. Return rank values greater than specified value using CONTAINSTABLE

This 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 with a category name of 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

See Also