FREETEXTTABLE (T-SQL)

Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning but not the exact wording of the text in the specified freetext_string. FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement like a regular table name.

Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) for each row.

Syntax

FREETEXTTABLE (table, {column | *}, 'freetext_string')

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 that resides within table. Columns of the character string data types are valid columns for full-text searching.

*

Specifies that all columns that have been registered for full-text searching should be used to search for the given freetext_string.

freetext_string

Is the text to search for in the specified column. Variables cannot be used.

Remarks

FREETEXTTABLE uses the same search conditions as the FREETEXT predicate.

Like CONTAINSTABLE, the table returned has columns named KEY and RANK, which are referenced within the query to obtain the appropriate rows and use the row ranking values.

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

Permissions

FREETEXTTABLE can be invoked only by users with appropriate SELECT privileges for the specified table or the referenced columns of the table.

Examples

This example returns the category name and description of all categories that relate to sweet, candy, bread, dry, and meat.

USE Northwind

SELECT FT_TBL.CategoryName,

    FT_TBL.Description,

    KEY_TBL.RANK

FROM Categories AS FT_TBL INNER JOIN

    FREETEXTTABLE(Categories, Description,

    'sweetest candy bread and dry meat') AS KEY_TBL

    ON FT_TBL.CategoryID = KEY_TBL.[KEY]

GO

See Also