Jaa

CONTAINSTABLE (Transact-SQL)

  • Artikkeli

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

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

Arguments

  • table
    Is the name of the table that has been marked for full-text querying. table can be a one-, two-, three-, or four-part database object name.
  • column_name
    Is the name of the column to search that resides in table. Columns of type char, varchar, nchar, nvarchar, text, ntext, image, xml, binary, and varbinary are valid columns for full-text searching.
  • column_list
    Indicates that several columns, separated by a comma, can be specified. column_list must be enclosed in parentheses. Unless language_term is specified, the language of all columns of column_list must be the same.
  • *
    Specifies that all columns in the table registered for full-text searching should be used to search for the given contains search condition. If more than one table is in the FROM clause, * must be qualified by the table name. Unless language_term is specified, the language of all columns of the table must be the same.

  • LANGUAGE language_term
    Is the language whose resources will be used for wordbreaking, stemming, and thesaurus and noise-word removal as part of the CONTAINS query. This parameter is optional and can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of a language. If language_term is specified, the language it represents will be applied to all elements of the search condition. If no value is specified, the column full-text language is used.

    When specified as a string, language_term corresponds to the alias column value in the syslanguages system table. The string must be enclosed in single quotation marks, as in 'language_term'. When specified as an integer, language_term is the actual LCID that identifies the language. When specified as a hexadecimal value, language_term is 0x followed by the hexadecimal value of the LCID. The hexadecimal value must not exceed eight digits, including leading zeros.

    If the value is in double-byte character set (DBCS) format, Microsoft SQL Server will convert it to Unicode.

    If the language specified is not valid or there are no resources installed that correspond to that language, SQL Server returns an error. To use the neutral language resources, specify 0x0 as language_term.

  • top_n_by_rank
    Specifies that only the nhighest ranked matches, in descending order, are returned. Applies only when an integer value, n, is specified. If additional filtering is performed, then less than n results may be returned.
  • <contains_search_condition>
    Specifies the text to search for in column_name and the conditions for a match. For more information, see CONTAINS (Transact-SQL).

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

Permissions

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

Examples

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.

Note

To run this example, you will have to install the Northwind database. For information about how to install the Northwind database, see Downloading Northwind and pubs Sample Databases.

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.

Note

To run this example, you will have to install the Northwind database. For information about how to install the Northwind database, see Downloading Northwind and pubs Sample Databases.

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

Note

To run this example, you will have to install the Northwind database. For information about how to install the Northwind database, see Downloading Northwind and pubs Sample Databases.

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.

See Also

Reference

CONTAINS (Transact-SQL)
Rowset Functions (Transact-SQL)
SELECT (Transact-SQL)
WHERE (Transact-SQL)

Other Resources

Querying SQL Server Using Full-Text Search

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the example for the LANGUAGE argument.