CONTAINS (Transact-SQL)CONTAINS (Transact-SQL)

适用对象:yesSQL Server(从 2008 版开始)yesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server 中搜索单个词和短语的精确或模糊(不太精确的)匹配项、在一定差别范围内的相近词或加权匹配项。Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL ServerSQL Server. CONTAINS 是一个谓词,用于在 Transact-SQLTransact-SQL SELECT 语句的 WHERE 子句中对包含基于字符的数据类型的全文检索列执行 SQL ServerSQL Server 全文搜索。CONTAINS is a predicate used in the WHERE clause of a Transact-SQLTransact-SQL SELECT statement to perform SQL ServerSQL Server full-text search on full-text indexed columns containing character-based data types.

CONTAINS 谓词可以搜索:CONTAINS can search for:

  • 词或短语。A word or phrase.

  • 词或短语的前缀。The prefix of a word or phrase.

  • 与另一个词相邻的词。A word near another word.

  • 由另一个词的词形变化而生成的词(例如,drive 一词是 drives、drove、driving 和 driven 词形变化的词干)。A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

  • 使用同义词库确定的另一个词的同义词(例如,“metal”一词可能有“aluminum”和“steel”等同义词)。A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

有关 SQL ServerSQL Server 支持的全文搜索形式的信息,请参阅使用全文搜索查询For information about the forms of full-text searches that are supported by SQL ServerSQL Server, see Query with Full-Text Search.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

CONTAINS (   
     {   
        column_name | ( column_list )   
      | *   
      | PROPERTY ( { column_name }, 'property_name' )    
     }   
     , '<contains_search_condition>'  
     [ , LANGUAGE language_term ]  
   )   
  
<contains_search_condition> ::=   
  {   
      <simple_term>   
    | <prefix_term>   
    | <generation_term>   
    | <generic_proximity_term>   
    | <custom_proximity_term>   
    | <weighted_term>   
    }   
  |   
    { ( <contains_search_condition> )   
        [ { <AND> | <AND NOT> | <OR> } ]   
        <contains_search_condition> [ ...n ]   
  }   
<simple_term> ::=   
     { word | "phrase" }  
  
<prefix term> ::=   
  { "word*" | "phrase*" }  
  
<generation_term> ::=   
  FORMSOF ( { INFLECTIONAL | THESAURUS } , <simple_term> [ ,...n ] )   
  
<generic_proximity_term> ::=   
  { <simple_term> | <prefix_term> } { { { NEAR | ~ }   
     { <simple_term> | <prefix_term> } } [ ...n ] }  
  
<custom_proximity_term> ::=   
  NEAR (   
     {  
        { <simple_term> | <prefix_term> } [ ,...n ]  
     |  
        ( { <simple_term> | <prefix_term> } [ ,...n ] )   
      [, <maximum_distance> [, <match_order> ] ]  
     }  
       )   
  
      <maximum_distance> ::= { integer | MAX }  
      <match_order> ::= { TRUE | FALSE }   
  
<weighted_term> ::=   
  ISABOUT   
   ( {   
        {   
          <simple_term>   
        | <prefix_term>   
        | <generation_term>   
        | <proximity_term>   
        }   
      [ WEIGHT ( weight_value ) ]   
      } [ ,...n ]   
   )   
  
<AND> ::=   
  { AND | & }  
  
<AND NOT> ::=   
  { AND NOT | &! }  
  
<OR> ::=   
  { OR | | }  
  

参数Arguments

column_namecolumn_name
FROM 子句中所指定的表的全文索引列的名称。Is the name of a full-text indexed column of the table specified in the FROM clause. 列可以是 char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 或 varbinary(max) 类型。The columns can be of type char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max).

column_listcolumn_list
指定以逗号分隔的两个或更多个列。Specifies two or more columns, separated by commas. column_list 必须用括号括起来。column_list must be enclosed in parentheses. 除非指定 language_term,否则 column_list 中所有列的语言必须相同。Unless language_term is specified, the language of all columns of column_list must be the same.

*
指定查询按给定的搜索条件在 FROM 子句中指定的表中搜索所有全文检索列。Specifies that the query searches all full-text indexed columns in the table specified in the FROM clause for the given search condition. CONTAINS 子句中的列必须来自包含全文索引的单个表。The columns in the CONTAINS clause must come from a single table that has a full-text index. 除非指定 language_term,否则表的所有列的语言必须相同。Unless language_term is specified, the language of all columns of the table must be the same.

PROPERTY ( column_name , 'property_name')PROPERTY ( column_name , 'property_name')
适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

指定在其中搜索指定搜索条件的文档属性。Specifies a document property on which to search for the specified search condition.

重要

要使查询返回任何行,必须在全文检索的搜索属性列表中指定 property_name,并且全文检索必须包含 property_name 的属性特定条目。For the query to return any rows, property_name must be specified in the search property list of the full-text index and the full-text index must contain property-specific entries for property_name. 有关详细信息,请参阅 使用搜索属性列表搜索文档属性For more information, see Search Document Properties with Search Property Lists.

LANGUAGE language_termLANGUAGE language_term
查询中用于断字、词干分析、同义词库扩展和替换,以及干扰词(或非索引字)删除的语言。Is the language to use for word breaking, stemming, thesaurus expansions and replacements, and noise-word (or stopword) removal as part of the query. 此参数可选。This parameter is optional.

如果将采用不同语言的文档一起作为二进制大型对象 (BLOB) 存储在单个列中,则给定文档的区域设置标识符 (LCID) 将决定为其内容编制索引时使用的语言。If documents of different languages are stored together as binary large objects (BLOBs) in a single column, the locale identifier (LCID) of a given document determines what language to use to index its content. 在对这种列进行查询时,指定 LANGUAGE language_term 可增大找到有效匹配项的可能性。When querying such a column, specifying LANGUAGE language_term can increase the probability of a good match.

language_term 可指定为与语言的 LCID 相对应的字符串、整数或十六进制值。language_term can be specified as a string, integer, or hexadecimal value corresponding to the LCID of a language. 如果指定了 language_term,则它表示的语言将应用于搜索条件的所有元素。If language_term is specified, the language it represents is applied to all elements of the search condition. 如果未指定值,则使用该列的全文语言。If no value is specified, the column full-text language is used.

如果指定为字符串,language_term 将对应于 sys.syslanguages (Transact-SQL) 兼容性视图中的 alias 列值。When specified as a string, language_term corresponds to the alias column value in the sys.syslanguages (Transact-SQL) compatibility view. 字符串必须用单引号引起来,如 'language_term'。The string must be enclosed in single quotation marks, as in 'language_term'. 如果指定为整数,则 language_term 就是标识该语言的实际 LCID。When specified as an integer, language_term is the actual LCID that identifies the language. 如果指定为十六进制值,则 language_term 将以 0x 开头,后跟 LCID 的十六进制值。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.

如果该值是双字节字符集 (DBCS) 格式,则 SQL ServerSQL Server 会将其转换为 Unicode 格式。If the value is in double-byte character set (DBCS) format, SQL ServerSQL Server converts it to Unicode.

如果指定的语言无效,或者未安装对应于该语言的资源,SQL ServerSQL Server 将返回错误。If the language specified is not valid or there are no resources installed that correspond to that language, SQL ServerSQL Server returns an error. 若要使用非特定语言资源,请将 0x0 指定为 language_term。To use the neutral language resources, specify 0x0 as language_term.

<contains_search_condition><contains_search_condition>
指定要在 column_name 中搜索的文本和匹配条件。Specifies the text to search for in column_name and the conditions for a match.

<contains_search_condition> 属于 nvarchar.<contains_search_condition> is nvarchar. 将另一个字符数据类型用作输入时,将发生隐式转换。An implicit conversion occurs when another character data type is used as input. 不能使用大型字符串数据类型 nvarchar(max) 和 varchar(max)。Large string data types nvarchar(max) and varchar(max) cannot be used. 在下面的示例中,@SearchWord 变量(被定义为 varchar(30))导致 CONTAINS 谓词中发生隐式转换。In the following example, the @SearchWord variable, which is defined as varchar(30), causes an implicit conversion in the CONTAINS predicate.

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

由于“参数截取”跨转换无效,因此请使用 nvarchar 以获得更好性能。Because "parameter sniffing" does not work across conversion, use nvarchar for better performance. 本示例将 @SearchWord 声明为 nvarchar(30)In the example, declare @SearchWord as nvarchar(30).

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

对于生成非最佳计划的情况,还可以使用 OPTIMIZE FOR 查询提示。You can also use the OPTIMIZE FOR query hint for cases in which a non optimal plan is generated.

wordword
不带空格或标点符号的字符串。Is a string of characters without spaces or punctuation.

phrasephrase
在每个词之间有空格的一个或多个词。Is one or more words with spaces between each word.

备注

某些语言(如亚洲一些地区的书面语言)的短语可以由彼此之间没有空格的一个或多个词组成。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><simple_term>
指定词或短语的完全匹配项。Specifies a match for an exact word or a phrase. 有效的简单字词示例有 "blue berry"、blueberry 和 "Microsoft SQL Server"。Examples of valid simple terms are "blue berry", blueberry, and "Microsoft SQL Server". 应该使用双引号 ("") 将短语引起来。Phrases should be enclosed in double quotation marks (""). 短语中的词在数据库列中出现的顺序必须与 <contains_search_condition> 中指定的顺序相同。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. 全文检索列中的干扰词(或非索引字)(例如 a、and 或 the)不会存储在全文检索中。Noise words (or stopwords) (such as a, and, or the) in full-text indexed columns are not stored in the full-text index. 如果在单个词搜索中使用了干扰词,SQL ServerSQL Server 将返回错误消息,指出查询仅包含干扰词。If a noise word is used in a single word search, SQL ServerSQL Server returns an error message indicating that the query contains only noise words. SQL ServerSQL Server 在每个 SQL ServerSQL Server 实例的 \Mssql\Binn\FTERef 目录下有一个标准的干扰词列表。includes a standard list of noise words in the directory \Mssql\Binn\FTERef of each instance of SQL ServerSQL Server.

标点将被忽略。Punctuation is ignored. 因此,CONTAINS(testing, "computer failure") 将匹配包含 "Where is my computer? Therefore, CONTAINS(testing, "computer failure") matches a row with the value, "Where is my computer? Failure to find it would be expensive" 这个值的行。Failure to find it would be expensive." 有关断字符行为的详细信息,请参阅配置和管理断字符和词干分析器以便搜索For more information on word-breaker behavior, see Configure and Manage Word Breakers and Stemmers for Search.

<prefix_term><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. 该子句应按以下方式指定:CONTAINS (column, '"text*"')The clause should be specified this way: CONTAINS (column, '"text*"'). 星号可匹配词或短语所含根词的 0 个、1 个或多个字符。The asterisk matches zero, one, or more characters (of the root word or words in the word or phrase). 如果文本和星号不用英文双引号分隔,则谓词将读取 CONTAINS (column, 'text*'),全文搜索会将星号看作字符,搜索 text* 的完全匹配项。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.

如果 <prefix_term> 是一个短语,则该短语中包含的每个词都将被看成一个单独的前缀。When <prefix_term> is a phrase, each word contained in the phrase is considered to be a separate prefix. 因此,指定了一个 "local wine*" 前缀字词的查询,将匹配所有包含 "local winery"、"locally wined and dined" 等文本的行。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><generation_term>
包含的简单字词包括要搜索的原始词的变体时,指定词的匹配项。Specifies a match of words when the included simple terms include variants of the original word for which to search.

INFLECTIONALINFLECTIONAL
指定要对指定的简单字词使用与语言相关的词干分析器。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. 如果指定了 language_term,则使用与该语言对应的词干分析器。If language_term is specified, the stemmer corresponding to that language is used.

<generation_term> 中的给定 <simple_term> 将不会匹配名词和动词。A given <simple_term> within a <generation_term> will not match both nouns and verbs.

THESAURUSTHESAURUS
指定使用对应于列全文语言或指定的查询语言的同义词库。Specifies that the thesaurus corresponding to the column full-text language, or the language specified in the query is used. 最长模式或来自 <simple_term> 的模式将与同义词库匹配,并生成其他字词以扩展或替换原始模式。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. 如果找不到与 <simple_term> 完全或部分匹配的匹配项,不匹配部分将被视为 simple_term。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 Configure and Manage Thesaurus Files for Full-Text Search.

<generic_proximity_term><generic_proximity_term>
指定词或短语的匹配项必须处于所搜索的文档中。Specifies a match of words or phrases that must be in the document that is being searched.

重要

此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.我们建议使用 <custom_proximity_term>。We recommend that you use <custom_proximity_term>.

NEAR | ~NEAR | ~
指示 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. 您必须指定两个搜索词。You must specify two search terms. 给定的搜索词可以是由双引号分隔的单个词或短语 ("phrase")。A given search term can be either a single word or a phrase that is delimited by double quotation marks ("phrase").

可将多个邻近词链接起来,例如 a NEAR b NEAR ca ~ b ~ cSeveral 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.

例如,CONTAINS(*column_name*, 'fox NEAR chicken')CONTAINSTABLE(*table_name*, *column_name*, 'fox ~ chicken') 均将返回指定列中同时包含“fox”和“chicken”的所有文档。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". 此外,CONTAINSTABLE 还会按照 "fox" 和 "chicken" 的邻近程度返回每个文档的排名。In addition, CONTAINSTABLE returns a rank for each document based on the proximity of "fox" and "chicken". 例如,如果文档包含句子“The fox ate the chicken”,该文档的排名将很高,因为这两个词比其他文档近。For example, if a document contains the sentence, "The fox ate the chicken," its ranking would be high because the terms are closer to one another than in other documents.

有关通用临近词的详细信息,请参阅使用 NEAR 搜索与另一个词邻近的词For more information about generic proximity terms, see Search for Words Close to Another Word with NEAR.

<custom_proximity_term><custom_proximity_term>
适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

指定词或短语的匹配项,并且可以选择指定搜索词之间允许的最大距离。Specifies a match of words or phrases, and optionally, the maximum distance allowed between search terms. 你还可以指定必须按你指定的确切顺序查找搜索词 (<match_order>)。you can also specify that search terms must be found in the exact order in which you specify them (<match_order>).

给定的搜索词可以是由双引号分隔的单个词或短语 ("phrase")。A given search term can be either a single word or a phrase that is delimited by double quotation marks ("phrase"). 文档中必须包含每个指定的词才能返回匹配项。Every specified term must be in the document for a match to be returned. 您必须至少指定两个搜索词。You must specify at least two search terms. 最大的搜索词数为 64 个。The maximum number of search terms is 64.

默认情况下,自定义近似词返回包含指定词的行,而不考虑间隔的距离及其顺序。By default, the custom proximity term returns any rows that contain the specified terms regardless of the intervening distance and regardless of their order. 例如,若要匹配下面的查询,文档只需包含 term1 和 "term3 term4",它们可以处于任意位置以及采用任意顺序:For example, to match the following query, a document would simply need to contain term1 and "term3 term4" anywhere, in any order:

CONTAINS(column_name, 'NEAR(term1,"term3 term4")')  

可选的参数如下:The optional parameters are as follows:

<maximum_distance><maximum_distance>
指定要使字符串作为匹配项,字符串开头和结尾处的搜索词之间允许的最大距离。Specifies the maximum distance allowed between the search terms at the start and end of a string in order for that string to qualify as a match.

integerinteger
指定 0 到 4294967295 之间的正整数。Specifies a positive integer from 0 to 4294967295. 该值控制第一个和最后一个搜索词之间可以包含多少个非搜索词,不包括任何其他指定的搜索词。This value controls how many non-search terms can occur between the first and last search terms, excluding any additional specified search terms.

例如,以下查询按两种顺序搜索 AABB(最大距离为 5)。For example, the following query searches for AA and BB, in either order, within a maximum distance of five.

CONTAINS(column_name, 'NEAR((AA,BB),5)')  

字符串 AA one two three four five BB 就是一个匹配项。The string AA one two three four five BB would be a match. 在以下示例中,查询指定了三个搜索词 AABBCC(最大距离为 5):In the following example, the query specifies for three search terms, AA, BB, and CC within a maximum distance of five:

CONTAINS(column_name, 'NEAR((AA,BB,CC),5)')  

此查询将匹配以下字符串(总距离为 5):This query would match the following string, in which the total distance is five:

BB one two CC three four five A A

请注意,内部搜索词 CC 不计算在内。Notice that the inner search term, CC, is not counted.

MAXMAX
返回所有包含指定词的行,而不考虑它们之间距离。Returns any rows that contain the specified terms regardless of the distance between them. 这是默认设置。This is the default.

<match_order><match_order>
指定词是否必须按指定顺序出现,搜索查询才会返回这些词。Specifies whether the terms must occur in the specified order to be returned by a search query. 若要指定 <match_order>,还必须指定 <maximum_distance>。To specify <match_order>, you must also specify <maximum_distance>.

<match_order> 采用下列值之一:<match_order> takes one of the following values:

TRUETRUE
强制在词中使用指定的顺序。Enforces the specified order within terms. 例如,NEAR(A,B) 仅匹配 A ... BFor example, NEAR(A,B) would match only A ... B.

FALSEFALSE
忽略指定的顺序。Ignores the specified order. 例如,NEAR(A,B) 匹配 A ... BB ... AFor example, NEAR(A,B) would match both A ... B and B ... A.

这是默认设置。This is the default.

例如,以下邻近词按指定顺序搜索“Monday”、“Tuesday”和“Wednesday”词,而不考虑它们之间的距离:For example, the following proximity term searches the words "Monday", "Tuesday", and "Wednesday" in the specified order with regardless of the distance between them:

CONTAINS(column_name, 'NEAR ((Monday, Tuesday, Wednesday), MAX, TRUE)')  

有关使用自定义临近词的详细信息,请参阅使用 NEAR 搜索与另一个词邻近的词For more information about using custom proximity terms, see Search for Words Close to Another Word with NEAR.

<weighted_term><weighted_term>
指定(由查询返回的)匹配行与一组词和短语匹配,每个词和短语有一个可选的加权值。Specifies that the matching rows (returned by the query) match a list of words and phrases, each optionally given a weighting value.

ISABOUTISABOUT
指定 <weighted_term> 关键字。Specifies the <weighted_term> keyword.

WEIGHT(weight_value)WEIGHT(weight_value)
指定介于 0.0 和 1.0 之间的加权值。Specifies a weight value, which is a number from 0.0 through 1.0. <weighted_term> 中的每个部分可能包含 weight_value。Each component in <weighted_term> may include a weight_value. 使用 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 不影响 CONTAINS 查询的结果,但 WEIGHT 会影响 CONTAINSTABLE 查询中的排名。WEIGHT does not affect the results of CONTAINS queries, but WEIGHT impacts rank in CONTAINSTABLE queries.

备注

不管操作系统的区域设置如何,小数点分隔符始终为句点。The decimal separator is always a period, regardless of the operating system locale.

{ AND | & } | { AND NOT | &!{ AND | & } | { AND NOT | &! } | { OR | | }} | { OR | | }
指定两个包含搜索条件之间的逻辑运算。Specifies a logical operation between two contains search conditions.

{ AND | & }{ AND | & }
指示匹配项必须满足这两个包含搜索条件。Indicates that the two contains search conditions must be met for a match. 可以使用 And 符 (&) 代替关键字 AND 来表示 AND 运算符。The ampersand symbol (&) may be used instead of the AND keyword to represent the AND operator.

{ AND NOT | &!{ AND NOT | &! }}
指示匹配项中不能出现第二个搜索条件。Indicates that the second search condition must not be present for a match. 可以使用 And 符后跟感叹号 (&!) 代替关键字 AND NOT 来表示 AND NOT 运算符。The ampersand followed by the exclamation mark symbol (&!) may be used instead of the AND NOT keyword to represent the AND NOT operator.

{ OR | | }{ OR | | }
指示匹配项必须满足这两个包含搜索条件之一。Indicates that either of the two contains search conditions must be met for a match. 可以使用竖线符号 (|) 代替关键字 OR 来表示 OR 运算符。The bar symbol (|) may be used instead of the OR keyword to represent the OR operator.

如果 <contains_search_condition> 包含带括号的组,则首先计算这些带括号的组。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 用在 AND 之前。NOT is applied before AND.

  • NOT 只能出现在 AND 之后,如在 AND NOT 中。NOT can only occur after AND, as in AND NOT. 不允许使用 OR NOT 运算符。The OR NOT operator is not allowed. 不能在首个字词前指定 NOT。NOT cannot be specified before the first term. 例如,CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ) 就是无效的。For example, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ) is not valid.

  • AND 用在 OR 之前。AND is applied before OR.

  • 相同类型的 Boolean 运算符(AND、OR)可以结合使用,因此可以按任意顺序应用。Boolean operators of the same type (AND, OR) are associative and can therefore be applied in any order.

nn
一个占位符,指示可在其中指定多个 CONTAINS 搜索条件和搜索词。Is a placeholder indicating that multiple CONTAINS search conditions and terms within them can be specified.

一般备注General Remarks

全文谓词和函数作用于 FROM 谓词所示的单个表。Full-text predicates and functions work on a single table, which is implied in the FROM predicate. 若要对多个表进行搜索,请在 FROM 子句中使用联接表,以搜索由两个或更多个表的乘积构成的结果集。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.

当数据库兼容级别设为 100 时,不允许在 OUTPUT 子句中使用全文谓词。Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.

查询远程服务器Querying Remote Servers

可以在 CONTAINS 或 FREETEXT 谓词中使用由四部分组成的名称对链接服务器上的目标表的全文索引列进行查询。You can use a four-part name in the CONTAINS or FREETEXT predicate to query full-text indexed columns of the target tables on a linked server. 若要准备远程服务器以接收全文查询,请在远程服务器上的目标表和列上创建全文索引,然后将该远程服务器添加为链接服务器。To prepare a remote server to receive full-text queries, create a full-text index on the target tables and columns on the remote server and then add the remote server as a linked server.

与全文搜索不同,LIKETransact-SQLTransact-SQL 谓词仅对字符模式有效。In contrast to full-text search, the LIKETransact-SQLTransact-SQL predicate works on character patterns only. 另外,不能使用 LIKE 谓词来查询格式化的二进制数据。Also, you cannot use the LIKE predicate to query formatted binary data. 此外,对大量非结构化的文本数据执行 LIKE 查询要比对相同数据执行同样的全文查询慢得多。Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. 对数百万行文本数据进行的 LIKE 查询可能需要几分钟的时间才能返回结果;而对于同样的数据,全文查询只需要几秒甚至更少的时间,具体取决于返回的行数及其大小。A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned and their size. 另一个考虑因素是 LIKE 仅对整个表执行简单模式扫描。Another consideration is that LIKE performs only a simple pattern scan of an entire table. 相反,全文查询可识别语言,它在索引和查询时应用特定的转换,例如,筛选非索引字并进行同义词库和变形扩展。A full-text query, in contrast, is language aware, applying specific transformations at index and query time, such as filtering stopwords and making thesaurus and inflectional expansions. 这些转换可帮助全文查询改进其撤回以及结果的最终排名。These transformations help full-text queries improve their recall and the final ranking of their results.

可通过指定一组要搜索的列来查询多个列。You can query multiple columns by specifying a list of columns to search. 这些列必须来自同一个表。The columns must be from the same table.

例如,以下 CONTAINS 查询在 AdventureWorks2012AdventureWorks2012 示例数据库的 Production.Product 表的 NameColor 列中搜索 Red 一词。For example, the following CONTAINS query searches for the term Red in the Name and Color columns of the Production.Product table of the AdventureWorks2012AdventureWorks2012 sample database.

Use AdventureWorks2012;  
GO  
SELECT Name, Color   
FROM Production.Product  
WHERE CONTAINS((Name, Color), 'Red');  

示例Examples

A.A. 将 CONTAINS 与 <simple_term> 一起使用Using CONTAINS with <simple_term>

下面的示例查找包含 $80.99 一词且价格为 Mountain的所有产品。The following example finds all products with a price of $80.99 that contain the word Mountain.

USE AdventureWorks2012;  
GO  
SELECT Name, ListPrice  
FROM Production.Product  
WHERE ListPrice = 80.99  
   AND CONTAINS(Name, 'Mountain');  
GO  

B.B. 将 CONTAINS 和短语与 <simple_term> 一起使用Using CONTAINS and phrase with <simple_term>

以下示例返回包含短语 MountainRoad 的所有产品。The following example returns all products that contain either the phrase Mountain or Road.

USE AdventureWorks2012;  
GO  
SELECT Name  
FROM Production.Product  
WHERE CONTAINS(Name, ' Mountain OR Road ')  
GO  

C.C. 将 CONTAINS 与 <prefix_term> 一起使用Using CONTAINS with <prefix_term>

下面的示例返回的所有产品名称中,其 Name 列中至少有一个词以前辍 chain 开头。The following example returns all product names with at least one word starting with the prefix chain in the Name column.

USE AdventureWorks2012;  
GO  
SELECT Name  
FROM Production.Product  
WHERE CONTAINS(Name, ' "Chain*" ');  
GO  

D.D. 将 CONTAINS 和 OR 与 <prefix_term> 一起使用Using CONTAINS and OR with <prefix_term>

下面的示例将返回包含以 chainfull 为前缀的字符串的所有类别说明。The following example returns all category descriptions containing strings with prefixes of either chain or full.

USE AdventureWorks2012;  
GO  
SELECT Name  
FROM Production.Product  
WHERE CONTAINS(Name, '"chain*" OR "full*"');  
GO  

E.E. 将 CONTAINS 与 <proximity_term> 一起使用Using CONTAINS with <proximity_term>

适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

以下示例在 Production.ProductReview 表中搜索包含 bike 一词、在“control”词的 10 个词范围内且使用指定顺序(即,“bike”排在“control”前面)的所有注释。The following example searches the Production.ProductReview table for all comments that contain the word bike within 10 terms of the word "control" and in the specified order (that is, where "bike" precedes "control").

USE AdventureWorks2012;  
GO  
SELECT Comments  
FROM Production.ProductReview  
WHERE CONTAINS(Comments , 'NEAR((bike,control), 10, TRUE)');  
GO  

F.F. 将 CONTAINS 与 <generation_term> 一起使用Using CONTAINS with <generation_term>

下面的示例将搜索包含单词 ride 的各种形式(如 riding 和 ridden 等)的所有产品。The following example searches for all products with words of the form ride: riding, ridden, and so on.

USE AdventureWorks2012;  
GO  
SELECT Description  
FROM Production.ProductDescription  
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');  
GO  

G.G. 将 CONTAINS 与 <weighted_term> 一起使用Using CONTAINS with <weighted_term>

以下示例搜索包含 performancecomfortablesmooth 词并为每个词指定不同加权的所有产品名称。The following example searches for all product names containing the words performance, comfortable, or smooth, and different weights are given to each word.

USE AdventureWorks2012;  
GO  
SELECT Description  
FROM Production.ProductDescription  
WHERE CONTAINS(Description, 'ISABOUT (performance weight (.8),   
comfortable weight (.4), smooth weight (.2) )' );  
GO  

H.H. 将 CONTAINS 与变量一起使用Using CONTAINS with variables

以下示例使用变量替代具体的搜索词。The following example uses a variable instead of a specific search term.

USE AdventureWorks2012;  
GO  
DECLARE @SearchWord nvarchar(30)  
SET @SearchWord = N'Performance'  
SELECT Description   
FROM Production.ProductDescription   
WHERE CONTAINS(Description, @SearchWord);  
GO  

I.I. 将 CONTAINS 与逻辑运算符 (AND) 一起使用Using CONTAINS with a logical operator (AND)

下面的示例使用 AdventureWorks2012AdventureWorks2012 数据库的 ProductDescription 表。The following example uses the ProductDescription table of the AdventureWorks2012AdventureWorks2012 database. 该查询使用 CONTAINS 谓词搜索 ID 不等于 5 并且同时包含 Aluminumspindle 词的说明。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. 该搜索条件使用 AND 布尔运算符。The search condition uses the AND Boolean operator.

USE AdventureWorks2012;  
GO  
SELECT Description  
FROM Production.ProductDescription  
WHERE ProductDescriptionID <> 5 AND  
   CONTAINS(Description, 'Aluminum AND spindle');  
GO  

J.J. 使用 CONTAINS 验证行插入操作Using CONTAINS to verify a row insertion

下面的示例在 SELECT 子查询中使用 CONTAINS。The following example uses CONTAINS within a SELECT subquery. 该查询将使用 AdventureWorks2012AdventureWorks2012 数据库获取 ProductReview 表中针对某一特定循环的所有注释的注释值。Using the AdventureWorks2012AdventureWorks2012 database, the query obtains the comment value of all the comments in the ProductReview table for a particular cycle. 该搜索条件使用 AND 布尔运算符。The search condition uses the AND Boolean operator.

USE AdventureWorks2012;  
GO  
INSERT INTO Production.ProductReview   
  (ProductID, ReviewerName, EmailAddress, Rating, Comments)   
VALUES  
  (780, 'John Smith', 'john@fourthcoffee.com', 5,   
'The Mountain-200 Silver from AdventureWorks2008 Cycles meets and exceeds expectations. I enjoyed the smooth ride down the roads of Redmond');  
  
-- Given the full-text catalog for these tables is Adv_ft_ctlg,   
-- with change_tracking on so that the full-text indexes are updated automatically.  
WAITFOR DELAY '00:00:30';     
-- Wait 30 seconds to make sure that the full-text index gets updated.  
  
SELECT r.Comments, p.Name  
FROM Production.ProductReview AS r  
JOIN Production.Product AS p   
    ON r.ProductID = p.ProductID  
    AND r.ProductID = (SELECT ProductID  
FROM Production.ProductReview  
WHERE CONTAINS (Comments,   
    ' AdventureWorks2008 AND   
    Redmond AND   
    "Mountain-200 Silver" '));  
GO  

K.K. 查询文档属性Querying on a document property

适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

以下查询在 Title 表的 Document 列中搜索索引属性 Production.DocumentThe following query searches on an indexed property, Title, in the Document column of the Production.Document table. 该查询仅返回 Title 属性包含字符串 Maintenance Repair 的文档。The query returns only documents whose Title property contains the string Maintenance or Repair.

备注

要使属性搜索返回行,在编制索引过程中分析列的一个或多个筛选器必须提取指定的属性。For a property-search to return rows, the filter or filters that parse the column during indexing must extract the specified property. 另外,必须配置指定表的全文索引以包含该属性。Also, the full-text index of the specified table must have been configured to include the property. 有关详细信息,请参阅 使用搜索属性列表搜索文档属性For more information, see Search Document Properties with Search Property Lists.

Use AdventureWorks2012;  
GO  
SELECT Document 
FROM Production.Document  
WHERE CONTAINS(PROPERTY(Document,'Title'), 'Maintenance OR Repair');  
GO  

另请参阅See Also

全文搜索入门 Get Started with Full-Text Search
创建和管理全文目录 Create and Manage Full-Text Catalogs
CREATE FULLTEXT CATALOG (Transact-SQL) CREATE FULLTEXT CATALOG (Transact-SQL)
CREATE FULLTEXT INDEX (Transact-SQL) CREATE FULLTEXT INDEX (Transact-SQL)
创建和管理全文索引 Create and Manage Full-Text Indexes
使用全文搜索查询 Query with Full-Text Search
CONTAINSTABLE (Transact-SQL) CONTAINSTABLE (Transact-SQL)
FREETEXT (Transact-SQL) FREETEXT (Transact-SQL)
FREETEXTTABLE (Transact-SQL) FREETEXTTABLE (Transact-SQL)
使用全文搜索查询 Query with Full-Text Search
全文搜索 Full-Text Search
创建全文搜索查询 (Visual Database Tools) Create Full-Text Search Queries (Visual Database Tools)
WHERE (Transact-SQL) WHERE (Transact-SQL)
使用搜索属性列表搜索文档属性Search Document Properties with Search Property Lists