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
1 out of 2 rated this helpful - Rate this topic

FREETEXTTABLE

SQL Server 2000

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.

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

See Also

CONTAINS

CONTAINSTABLE

FREETEXT

Full-text Querying SQL Server Data

Rowset Functions

SELECT

WHERE

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.