Is a predicate used in a WHERE clause to search columns containing character-based data types for values that match the meaning and not just the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.
Separates the string into individual words based on word boundaries (word-breaking).
Generates inflectional forms of the words (stemming).
Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.
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.
Full-text queries using FREETEXT are less precise than those full-text queries using CONTAINS. The SQL Server full-text search engine identifies important words and phrases. No special meaning is given to any of the reserved keywords or wildcard characters that typically have meaning when specified in the <contains_search_condition> parameter of the CONTAINS predicate.
FREETEXT is not recognized as a keyword if the compatibility level is less than 70. For more information, see sp_dbcmptlevel (Transact-SQL).
Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
A. 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 AdventureWorks; GO SELECT Title FROM Production.Document WHERE FREETEXT (Document, 'vital safety components' ); GO
B. Using FREETEXT with variables
The following example uses a variable instead of a specific search term.
USE AdventureWorks; GO DECLARE @SearchWord nvarchar(30); SET @SearchWord = N'high-performance'; SELECT Description FROM Production.ProductDescription WHERE FREETEXT(Description, @SearchWord); GO