Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

CONTAINSTABLE

SQL Server 2000

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 > '
    
[ , 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 , < 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-, or three-part database object name. For more information, see Transact-SQL Syntax Conventions. table cannot specify a server name and cannot be used in queries against linked servers.

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

top_n_by_rank

Specifies that only the n highest ranked matches, in descending order, are returned. Applies only when an integer value, n, is specified.

<contains_search_condition>

Specifies some text to search for in column. 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 (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.

  • 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 "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
C. Return top 10 ranked results using CONTAINSTABLE and Top_n_by_rank

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

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]

See Also

CONTAINS

Full-text Querying SQL Server Data

Rowset Functions

SELECT

WHERE

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft