.png)
FREETEXTTABLE (Transact-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 only 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) and full-text key (KEY) for each row.
Full-text predicates and functions work on a single table, which is implied in the FROM predicate. To search on multiple tables, use a joined table in your FROM clause to search on a result set that is the product of two or more tables.
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 (Transact-SQL).
The following example returns the category name and description of all categories that relate to sweet, candy, bread, dry, or meat.
 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.CategoryName
,FT_TBL.Description
,KEY_TBL.RANK
FROM dbo.Categories AS FT_TBL
INNER JOIN FREETEXTTABLE(dbo.Categories, Description,
'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY];
GO
The following example is identical and shows the use of the LANGUAGElanguage_term and top_n_by_rank parameters.
USE Northwind;
SELECT FT_TBL.CategoryName
,FT_TBL.Description
,KEY_TBL.RANK
FROM dbo.Categories AS FT_TBL
INNER JOIN FREETEXTTABLE(dbo.Categories, Description,
'sweetest candy bread and dry meat',LANGUAGE 'English',2)
AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY];
GO
| Note |
|---|
The LANGUAGE language_term parameter is not required to use the top_n_by_rank parameter. |
