<contains_search_condition>
Specifies the text to search for in column_name
and the conditions for a match.
<contains_search_condition> 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 CONTAINS predicate.
Copy Code
USE AdventureWorks;
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='performance'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @SearchWord);
Because "parameter sniffing" does not work across conversion, use
nvarchar for better performance.
In the example, declare @SearchWord as nvarchar(30).
Copy Code
USE AdventureWorks;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @SearchWord);
You can also use the OPTIMIZE FOR query hint for cases in which a non optimal plan
is generated.
word
Is a string of characters without spaces or punctuation.
phrase
Is one or more words with spaces between each word.
|
 Note:
|
|
Some languages, such
as those written in some parts of Asia, can have phrases that consist of one or
more words without spaces between them.
|
<simple_term>
Specifies a match for an exact word or a phrase. Examples of valid simple terms
are "blue berry", blueberry, and "Microsoft SQL Server". Phrases
should be enclosed in double quotation marks (""). Words in a phrase must
appear in the same order as specified in <contains_search_condition>
as they appear in the database column. The search for characters in the word or
phrase is not case-sensitive. Noise words (or stopwords) (such as a, and, or the)
in full-text indexed columns are not stored in the full-text index. If a noise word
is used in a single word search, SQL Server returns an error message indicating
that the query contains only noise words. SQL Server includes a standard list of
noise words in the directory \Mssql\Binn\FTERef of each instance of SQL Server.
Punctuation is ignored. Therefore, CONTAINS(testing, "computer failure")
matches a row with the value, "Where is my computer? Failure to find it would
be expensive." For more information on word-breaker behavior, see Word Breakers and Stemmers.
<prefix_term>
Specifies a match of words or phrases beginning with the specified text. Enclose
a prefix term in double quotation marks ("") and add an asterisk (*) before
the ending quotation mark, so that all text starting with the simple term specified
before the asterisk is matched. The clause should be specified this way:
CONTAINS (column,
'"text*"'). The asterisk matches zero, one, or more characters
(of the root word or words in the word or phrase). If the text and asterisk are
not delimited by double quotation marks, so the predicate reads CONTAINS (column, 'text*'),
full-text search considers the asterisk as a character and searches for exact matches
to text*.
The full-text engine will not find words with the asterisk (*) character because
word breakers typically ignore such characters.
When <prefix_term> is a
phrase, each word contained in the phrase is considered to be a separate prefix.
Therefore, a query specifying a prefix term of "local wine*" matches any
rows with the text of "local winery", "locally wined and dined",
and so on.
<generation_term>
Specifies a match of words when the included simple terms include variants of the
original word for which to search.
INFLECTIONAL
Specifies that the language-dependent stemmer is to be used on the specified simple
term. Stemmer behavior is defined based on stemming rules of each specific language.
The neutral language does not have an associated stemmer. The column language of
the columns being queried is used to refer to the desired stemmer. If
language_term is specified, the stemmer corresponding to that language
is used.
A given <simple_term> within
a <generation_term> will
not match both nouns and verbs.
THESAURUS
Specifies that the thesaurus corresponding to the column full-text language, or
the language specified in the query is used. The longest pattern or patterns from
the <simple_term> are matched
against the thesaurus and additional terms are generated to expand or replace the
original pattern. If a match is not found for all or part of the
<simple_term>, the non-matching portion is treated as a simple_term. For more information
on the full-text search thesaurus, see Thesaurus Configuration.
<proximity_term>
Specifies a match of words or phrases that must be in the document that is being
searched. Like the AND operator, <proximity_term>
requires both the search terms to exist in the document being searched.
NEAR | ~
Indicates that the word or phrase on each side of the NEAR or ~ operator must occur
in a document for a match to be returned. Several proximity terms can be chained,
as in a NEAR b NEAR c or a ~ b ~ c. Chained proximity terms
must all be in the document for a match to be returned.
When used in the CONTAINSTABLE function, the proximity of the search terms affects
the ranking of each document. The nearer the matched search terms are in a document,
the higher the ranking of the document. If matched search terms are >50 terms
apart, the rank returned on the document is 0.
For example, CONTAINS (column_name,
'fox NEAR chicken') and CONTAINSTABLE (table_name,
column_name, 'fox ~ chicken')
would both return any documents in the specified column that contain both "fox"
and "chicken". In addition, CONTAINSTABLE returns a rank for each document
based on the proximity of "fox" and "chicken". For example,
if a document contains the sentence, "The fox ate the chicken," its ranking
would be high.
NEAR indicates the logical distance between terms, rather than the absolute distance
between them. For example, terms within different phrases or sentences within a
paragraph are treated as farther apart than terms in the same phrase or sentence,
regardless of their actual proximity, on the assumption that they are less related.
Likewise, terms in different paragraphs are treated as being even farther apart.
<weighted_term>
Specifies that the matching rows (returned by the query) match a list of words and
phrases, each optionally given a weighting value.
ISABOUT
Specifies the <weighted_term>
keyword.
WEIGHT(weight_value)
Specifies a weight value, which is a number from 0.0 through 1.0. Each component
in <weighted_term> may
include a weight_value. weight_value is a way to change how
various portions of a query affect the rank value assigned to each row matching
the query. WEIGHT does not affect the results of CONTAINS queries, but WEIGHT impacts
rank in
CONTAINSTABLE queries.
|
Note:
|
|
The decimal separator
is always a period, regardless of the operating system locale.
|
{ AND | & } | { AND NOT | &! } | { OR | | }
Specifies a logical operation between two contains search conditions.
AND | &
Indicates that the two contains search conditions must be met for a match. The ampersand
symbol (&) may be used instead of the AND keyword to represent the AND operator.
AND NOT | &!
Indicates that the second search condition must not be present for a match. The
ampersand followed by the exclamation mark symbol (&!) may be used instead of
the AND NOT keyword to represent the AND NOT operator.
OR | |
Indicates that either of the two contains search conditions must be met for a match.
The bar symbol (|) may be used instead of the OR keyword to represent the OR operator.
When <contains_search_condition>
contains parenthesized groups, these parenthesized groups are evaluated first. After
evaluating parenthesized groups, these rules apply when using these logical operators
with contains search conditions:
·
NOT is applied before AND.
·
NOT can only occur after AND, as in AND NOT.
The OR NOT operator is not allowed. NOT cannot be specified before the first term.
For example,
CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' )
is not valid.
·
AND is applied before OR.
· Boolean
operators of the same type (AND, OR) are associative and can therefore be applied
in any order.
n
Is a placeholder indicating that multiple CONTAINS search conditions and terms within
them can be specified.