FREETEXTTABLE
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' [ , top_n_by_rank ] )
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 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.
top_n_by_rank
When an integer value, n, is specified, FREETEXTTABLE returns only the top n matches, ordered by rank.
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