Share via

FREETEXT (Transact-SQL)

  • Article

Is a predicate used to search columns containing character-based data types for values that match the meaning and not 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.

Topic link iconTransact-SQL Syntax Conventions

Syntax

FREETEXT ( { column_name | (column_list) | * } 
          , 'freetext_string' [ , LANGUAGE language_term ] )

Arguments

  • column_name
    Is the name of the column that has been registered for full-text searching. Columns of type char, varchar, nchar, nvarchar, text, ntext, image, xml, and varbinary(max) are valid columns for full-text searching.
  • column_list
    Indicates that several columns, separated by a comma, can be specified. column_list must be enclosed in parentheses. Unless language_term is specified, the language of all columns of column_list must be the same.
  • *
    Specifies that all columns that have been registered for full-text searching should be used to search for the given freetext_string. If more than one table is in the FROM clause, * must be qualified by the table name. Unless language_term is specified, the language of all columns of the table must be the same.

  • freetext_string
    Is text to search for in the column_name. Any text, including words, phrases or sentences, can be entered. Matches are generated if any term or the forms of any term is found in the full-text index.

    freetext_string is nvarchar. An implicit conversion occurs when another character data type is used as input. In the following example, the @SearchWord variable, which is defined as varchar(30), causes an implicit conversion in the FREETEXT predicate.

    USE AdventureWorks;
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE FREETEXT(Description, @SearchWord);

    Because "parameter sniffing" does not work across conversion, use nvarchar for better performance. In the example, declare @SearchWord as nvarchar(30).

    USE AdventureWorks;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE FREETEXT(Description, @SearchWord);

    You can also use the OPTIMIZE FOR query hint for cases in which a nonoptimal plan is generated.

    Unlike in the CONTAINS search condition where AND is a keyword, when used in freetext_string the word 'and' is considered a noise word and will be discarded.

    Use of WEIGHT, FORMSOF, wildcards, NEAR and other syntax is not allowed. freetext_string is wordbroken, stemmed, and passed through the thesaurus. If freetext_string is enclosed in double quotation marks, a phrase match is instead performed; stemming and thesaurus are not performed.

  • LANGUAGE language_term
    Is the language whose resources will be used for wordbreaking, stemming, and thesaurus and noise-word removal as part of the FREETEXT query. This parameter is optional and can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of a language. If language_term is specified, the language it represents will be applied to all elements of the search condition. If no value is specified, the column full-text language is used.

    When specified as a string, language_term corresponds to the alias column value in the syslanguages system table. The string must be enclosed in single quotation marks, as in 'language_term'. When specified as an integer, language_term is the actual LCID that identifies the language. When specified as a hexadecimal value, language_term is 0x followed by the hexadecimal value of the LCID. The hexadecimal value must not exceed eight digits, including leading zeros.

    If the value is in double-byte character set (DBCS) format, Microsoft SQL Server will convert it to Unicode.

    If the language specified is not valid or there are no resources installed that correspond to that language, Microsoft SQL Server returns an error. To use the neutral language resources, specify 0x0 as language_term.

Remarks

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).

Examples

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

See Also

Reference

CONTAINS (Transact-SQL)
CONTAINSTABLE (Transact-SQL)
Data Types (Transact-SQL)
FREETEXTTABLE (Transact-SQL)
WHERE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added information about avoiding conversion with the freetext_string values.