Full-Text Predicates and Functions Overview
Full-text queries use the full-text predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE). These support rich Transact-SQL syntax that supports a variety of forms of query terms. To write full-text queries, you must learn when and how to use these predicates and functions. This topic summarizes the predicates and the functions and discusses commonalities between the CONTAINS predicate and the CONTAINSTABLE function.
Overview of the Full-Text Predicates (CONTAINS and FREETEXT)
CONTAINS and FREETEXT are specified in the WHERE or HAVING clause of a SELECT statement. They can be combined with any of the other Transact-SQL predicates, such as LIKE and BETWEEN.
The CONTAINS and FREETEXT predicates return a TRUE or FALSE value. They can be used only to specify selection criteria for determining whether a given row matches the full-text query. Matching rows are returned in the result set.
When using CONTAINS or FREETEXT, you can specify either a single column, a list of columns, or all columns in the table to be searched. Optionally, you can specify the language whose resources will be used by given full-text query for word breaking and stemming, thesaurus lookups, and noise-word removal.
CONTAINS and FREETEXT are useful for different kind of matches, as follows:
Use CONTAINS (or CONTAINSTABLE) 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. When using CONTAINS, you must specify at least one search condition that specifies the text that you are searching for and the conditions that determine matches.
You can use logical operation between search conditions. For more information, see Using Boolean operators—AND, OR, AND NOT (in CONTAINS and CONTAINSTABLE), later in this topic.
Use FREETEXT (or FREETEXTTABLE) for matching the meaning, but not the exact wording, of specified words, phrases or sentences (the freetext string). Matches are generated if any term or form of any term is found in the full-text index of a specified column.
To execute queries against linked servers, you can use four-part names in either CONTAINS or FREETEXT. For more information, see Querying Linked Servers (Full-Text Search).
For information about the syntax and arguments of these predicates, see CONTAINS (Transact-SQL) and FREETEXT (Transact-SQL).
Note
Full-text predicates are not allowed in the OUTPUT Clause when the database compatibility level is set to 100.
Examples
A. Using CONTAINS with <simple_term>
The following example finds all products with a price of $80.99 that contain the word "Mountain".
USE AdventureWorks2008R2;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
AND CONTAINS(Name, 'Mountain');
GO
B. Using FREETEXT to search for words containing specified character values
The following example searches for all documents containing the words related to vital, safety, components.
USE AdventureWorks2008R2;
GO
SELECT Title
FROM Production.Document
WHERE FREETEXT (Document, 'vital safety components' );
GO
Overview of the Full-Text Functions (CONTAINSTABLE and FREETEXTTABLE)
The CONTAINSTABLE and FREETEXTTABLE functions are referenced like a regular table name in the FROM clause of a SELECT statement. They return a table of zero, one, or more rows that match the full-text query. The returned table contains only rows of the base table that match the selection criteria specified in the full-text search condition of the function.
Queries using one of these functions return a relevance ranking value (RANK) and full-text key (KEY) for each row, as follows:
KEY column
The KEY column returns unique values of the returned rows. The KEY column can be used to specify selection criteria.
RANK column
The RANK column returns a rank value for each row that indicates how well the row matched the selection criteria. The higher the rank value of the text or document in a row, the more relevant the row is for the given full-text query. Note that different rows can be ranked identically. You can limit the number of matches to be returned by specifying the optional top_n_by_rank parameter. For more information, see Limiting Ranked Result Sets (Full-Text Search) and How Search Query Results Are Ranked (Full-Text Search).
When using either of these functions, you must specify the base table that is to be full-text searched. As with the predicates, you can specify a single column, a list of columns, or all columns in the table to be searched, and optionally, the language whose resources will be used by given full-text query.
CONTAINSTABLE is useful for the same kinds of matches as CONTAINS, and FREETEXTTABLE is useful for the same kinds of matches as FREETEXT. For more information, see Overview of the Full-Text Predicates (CONTAINS and FREETEXT), earlier in this topic.When running queries that use the CONTAINSTABLE and FREETEXTTABLE functions you must explicitly join rows that are returned with the rows in the SQL Server base table.
For information about the syntax and arguments of these functions, see CONTAINSTABLE (Transact-SQL) and FREETEXTTABLE (Transact-SQL).
Examples
A. Using CONTAINSTABLE
The following 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;
GO
B. Using FREETEXTTABLE
The following example extends a FREETEXTTABLE query to return the highest ranked rows first and to add the ranking of each row to the select list. To specify the query, you must know that CategoryID is the unique key column for the Categories table.
USE Northwind;
GO
SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories AS FT_TBL
INNER JOIN
FREETEXTTABLE(Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
GO
Here is an extension of the same query that only returns rows with a rank value of 10 or greater:
USE Northwind;
GO
SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories FT_TBL
INNER JOIN
FREETEXTTABLE (Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK >= 10
ORDER BY KEY_TBL.RANK DESC;
GO
Using Boolean operators—AND, OR, AND NOT (in CONTAINS and CONTAINSTABLE)
The CONTAINS predicate and CONTAINSTABLE function use the same search conditions. Both support combining several search terms by using Boolean operators—AND, OR, AND NOT—to perform logical operations. You could use AND, for example, to find rows that contain both "latte" and "New York-style bagel". You can use AND NOT, for example, to find the rows that contain "bagel" but do not contain "cream cheese".
Note
In contrast, FREETEXT and FREETEXTTABLE treat the Boolean terms as words to be searched.
For information about combining CONTAINS with other predicates that use the logical operators AND, OR, and NOT, see Search Condition (Transact-SQL).
Example
The following example uses the ProductDescription table of the AdventureWorks2008R2 database. The query uses the CONTAINS predicate to search for descriptions in which the description ID is not equal to 5 and the description contains both the word "Aluminum" and the word "spindle." The search condition uses the AND Boolean operator.
USE AdventureWorks2008R2;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');
GO
Additional Considerations
Each full-text enabled table has a column that is used to enforce unique rows for the table (the uniquekey column). When writing the CONTAINSTABLE or FREETEXTTABLE type of full-text queries, you need the name of the unique key column. For more information, see How to: Inquire About the Full-Text Key Column (Transact-SQL).
Many query terms depend heavily on word-breaker behavior. To ensure that you are using the correct word breaker (and stemmer) and thesaurus file, we recommend that you specify the LANGUAGE argument. For more information, see Best Practices for Choosing a Language When Creating a Full-Text Index.
When defining a full-text query, the Full-Text Engine discards stopwords (also called noise words) from the search criteria. Stopwords are words such as "a," "and," "is," or "the," that can occur frequently but that typically do not help when searching for particular text. Stopwords are listed in a stoplist. Each full-text index is associated with a specific stoplist, which determines what stopwords are omitted from the query or the index at indexing time. For more information, see Stopwords and Stoplists.
Thesaurus matching occurs for CONTAINS and CONTAINSTABLE Transact-SQL queries that specify the FORMSOF THESAURUS clause and for FREETEXT and FREETEXTABLE queries by default.
See Also