CONTAINS (Transact-SQL)
這是一個在 WHERE 子句中，用來搜尋資料行的述語，這些資料行包含以字元為基礎的資料類型，用來進行下列各種比對：單字或片語完全或部分 (較不精確) 相符、各字相距在一定距離內，或加權相符。
在 SQL Server 中，您可以在 CONTAINS 或 FREETEXT 全文檢索述詞中，利用四部分名稱來針對連結伺服器執行查詢。
CONTAINS 可以搜尋下列項目：
單字或片語。
單字或片語的前置詞。
接近另一個單字的單字。
從另一個單字變化產生的單字 (如 drive 這個字是 drives、drove、driving 和 driven 等字的變化詞幹)。
使用同義字 (Thesaurus) 時，另一個單字的同義字 (Synonym) (例如，aluminum 和 steel 等字是 metal 的同義字)。
.gif "主題連結圖示")
語法
CONTAINS
( { column_name | ( column_list ) | * }
, '<contains_search_condition>'
[ , LANGUAGE language_term ]
)
<contains_search_condition> ::=
{ <simple_term>
| <prefix_term>
| <generation_term>
| <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 ] )
<proximity_term> ::=
{ <simple_term> | <prefix_term> }
{ { NEAR | ~ }
{ <simple_term> | <prefix_term> }
} [ ...n ]
<weighted_term> ::=
ISABOUT
( { {
<simple_term>
| <prefix_term>
| <generation_term>
| <proximity_term>
}
[ WEIGHT ( weight_value ) ]
} [ ,...n ]
)
<AND> ::=
{ AND | & }
<AND NOT> ::=
{ AND NOT | &! }
<OR> ::=
{ OR | | }
引數
column_name
這是在 FROM 子句中指定之資料表全文檢索索引資料行的名稱。資料行的類型可以是 char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 或 varbinary(max)。column_list
指定兩個或多個以逗號分隔的資料行。column_list 必須括在括號中。除非您指定了 language_term，否則，column_list 所有資料行的語言必須相同。*
指定查詢將會在 FROM 子句指定的資料表中，針對給定的搜尋條件搜尋所有全文檢索索引資料行。CONTAINS 子句中的資料行必須來自包含全文檢索索引的單一資料表。除非您指定了 language_term，否則，資料表所有資料行的語言必須相同。LANGUAGE language_term
這是在查詢中用於斷詞、確立詞幹、同義字擴充與取代，以及移除非搜尋字 (或停用字詞) 的語言。這個參數是選擇性的。如果不同語言的文件當做二進位大型物件 (BLOB) 一起儲存在單一資料行中，給定文件的地區設定識別碼 (LCID) 會判斷要建立其內容索引所使用的語言。查詢此種資料行時，指定 LANGUAGE language_term 可以增加完全相符的機率。
language_term 可以指定為對應於語言 LCID 的字串、整數或十六進位值。如果指定了 language_term，就會將它代表的語言套用至搜尋條件的所有元素上。如果未指定任何值，就會使用資料行全文檢索語言。
當指定為字串時，language_term 會對應到 sys.syslanguages (Transact-SQL) 相容性檢視中的 alias 資料行值。字串必須以單引號括住，如 'language_term'。當指定為整數時，language_term 是用於識別語言的實際 LCID。當指定為十六進位值時，language_term 是 0x，後面接著 LCID 的十六進位值。十六進位值不能超出 8 位數，開頭的零也包括在內。
如果這個值是雙位元組字集 (DBCS) 格式，SQL Server 會將它轉換成 Unicode。
如果指定的語言無效，或尚未安裝對應於這個語言的資源，SQL Server 就會傳回錯誤。若要使用中性語言資源，請在 language_term 中指定 0x0。
<contains_search_condition>
指定 column_name 中所要搜尋的文字，以及要比對的條件。<contains_search_condition> 是 nvarchar。當使用另一個字元資料類型當做輸入時，會發生隱含轉換。在下列範例中，定義為 varchar(30) 的 @SearchWord 變數會造成 CONTAINS 述詞中的隱含轉換。
USE AdventureWorks; GO DECLARE @SearchWord varchar(30) SET @SearchWord ='performance' SELECT Description FROM Production.ProductDescription WHERE CONTAINS(Description, @SearchWord);由於「參數探測」不適用於轉換，所以請使用 nvarchar 來獲得更好的效能。在此範例中，請將 @SearchWord 宣告為 nvarchar(30)。
USE AdventureWorks; GO DECLARE @SearchWord nvarchar(30) SET @SearchWord = N'performance' SELECT Description FROM Production.ProductDescription WHERE CONTAINS(Description, @SearchWord);當產生的並非最佳計畫時，也可以使用 OPTIMIZE FOR 查詢提示。
word
這是不含空格或標點符號的字元字串。phrase
這是一或多個單字，各個字之間有空格。[!附註]
部分語言 (如亞洲某些地區所撰寫的語言) 可能會有不用空格來分隔的一或多個單字所組成的片語。
<simple_term>
指定符合完整的單字或片語。"blue berry"、blueberry 和 "Microsoft SQL Server" 都是有效的簡單詞彙範例。片語應該用雙引號 ("") 括住。片語中的單字順序必須依照 <contains_search_condition> 所指定，符合它們在資料庫資料行出現的順序。在單字或片語中搜尋字元，並不區分大小寫。全文檢索索引資料行中的非搜尋字 (或停用字詞) (如 a、and 或 the) 不會儲存在全文檢索索引中。如果在單字的搜尋中使用非搜尋字，SQL Server 會傳回一則錯誤訊息，指出查詢只包含非搜尋字。在每個 SQL Server 執行個體的 \Mssql\Binn\FTERef 目錄中，SQL Server 都併入了一份標準非搜尋字清單。系統會忽略標點符號。因此，CONTAINS(testing, "computer failure") 將符合含有此值的資料列："Where is my computer?Failure to find it would be expensive"。如需有關斷詞工具行為的詳細資訊，請參閱＜斷詞工具與字幹分析器＞。
<prefix_term>
指定符合開頭是指定文字的單字或片語。請用雙引號 ("") 括住前置詞彙，在後引號的前面加上星號 (*)，以便找出所有開頭是星號前面所指定之簡單詞彙的文字。這個子句的指定方式應該如下：CONTAINS (column, '"text*"')。星號符合 (單字或片語中一或多個根單字的) 零個、一個或多個字元。如果並未用雙引號來分隔文字和星號，述詞便是 CONTAINS (column, 'text*')，此時全文檢索搜尋會將星號視為字元，因此，它會搜尋完全相符的 text*。全文檢索引擎並不會找到開頭是星號 (*) 的文字，因為斷詞工具通常會忽略這些字元。當 <prefix_term> 是片語時，片語所包含的每個單字都會被視為個別的前置詞。因此，指定前置詞彙 "local wine*" 的查詢可能會符合開頭是 "local winery"、"locally wined and dined" 等文字的任何資料列。
<generation_term>
指定符合「其中所包括的簡單詞彙含有要搜尋的原始單字之各種變化形」的單字。INFLECTIONAL
在指定的簡單詞彙上，指定所用之特定語言的字幹分析器。字幹分析器的行為是以每種特定語言的詞幹分析規則來定義的。中性語言並沒有相關聯的字幹分析器。系統會利用查詢之資料行的資料行語言來參考所需的字幹分析器。如果有指定 language_term，便會使用對應於這個語言的字幹分析器。<generation_term> 內給定的 <simple_term> 不會同時符合名詞和動詞。
THESAURUS
指定使用對應於全文檢索語言或查詢指定的語言之同義字。系統會利用 <simple_term> 中一或多個最長的模式來比對這些同義字，且會產生其他詞彙來擴充或取代原始模式。如果找不到 <simple_term> 的完全或部分相符，就會將不相符的部分當做 simple_term 來處理。如需有關全文檢索搜尋同義字的詳細資訊，請參閱＜同義字組態＞。<proximity_term>
指定必須符合要搜尋之文件中的單字或片語。如同 AND 運算子，<proximity_term> 需要兩個搜尋詞彙都存在於要搜尋的文件中。NEAR | ~
指出 NEAR 或 ~ 運算子兩側的單字或片語必須出現在文件中，才會傳回相符項目。您可以將數個相近詞彙鏈結起來，例如：a NEAR b NEAR c 或 a ~ b ~ c。鏈結的相近詞彙必須全部都在文件中，才會傳回相符項目。用於 CONTAINSTABLE 函數時，搜尋詞彙的距離會影響每個文件的排名。相符的搜尋詞彙在文件中的距離越近，文件的排名越高。如果相符之搜尋詞彙的距離為 >50 個詞彙，在文件上傳回的排名為 0。
例如，CONTAINS (column_name, 'fox NEAR chicken') 和 CONTAINSTABLE (table_name, column_name, 'fox ~ chicken') 會在指定的資料行中，傳回同時包含 "fox" 和 "chicken" 的任何文件。此外，CONTAINSTABLE 會根據 "fox" 和 "chicken" 的距離，傳回每個文件的排名。例如，如果某個文件包含 "The fox ate the chicken" 這個句子，其排名將會比較高。
NEAR 表示詞彙之間的邏輯距離，而非絕對距離。例如，在某個段落中，不同片語或句子內的詞彙會比相同片語或句子內的詞彙被視為距離較遠，不論其實際距離為何都一樣，不過前提是它們的關聯性較低。同樣地，不同段落中的詞彙則被視為距離更遠。
<weighted_term>
指定相符的資料列 (查詢所傳回) 會符合一份單字和片語清單，每個單字和片語各有其加權值。ISABOUT
指定 <weighted_term> 關鍵字。WEIGHT(weight_value)
指定加權值，它是 0.0 至 1.0 的數字。<weighted_term> 中的每個元件都可以包含 weight_value。weight_value 是一種方法，它可變更查詢的各個部分如何影響已指派給符合查詢之各個資料列的排名值。WEIGHT 不會影響 CONTAINS 查詢的結果，但 WEIGHT 會影響 CONTAINSTABLE 查詢中的排名。[!附註]
不論作業系統地區設定為何，小數分隔符號一定是句點。
{ AND | & } | { AND NOT | &!} | { OR | | }
指定兩個包含搜尋條件之間的邏輯運算。AND | &
指出相符項目必須符合這兩個包含搜尋條件。您可以利用 & 符號取代 AND 關鍵字來表示 AND 運算子。AND NOT | &!
指出相符項目不能有第二個搜尋條件。您可以利用 & 符號後面接著驚嘆號 (&!) 取代 AND NOT 關鍵字來表示 AND NOT 運算子。OR | |
指出相符項目必須符合兩個包含搜尋條件的其中之一。您可以利用垂直線符號 (|) 取代 OR 關鍵字來表示 OR 運算子。當 <contains_search_condition> 包含附加括號的群組時，會先評估這些附加括號的群組。在評估附加括號的群組之後，當在包含搜尋條件中使用這些邏輯運算子，便適用下列規則：
NOT 在 AND 前面。
NOT 只能在 AND 之後，如同 AND NOT。不允許 OR NOT 運算子。不能在第一個詞彙前面指定 NOT。例如，CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ) 無效。
AND 在 OR 前面。
同類型的 Boolean 運算子 (AND、OR) 是聯合的，因此，套用順序沒有任何限制。
- n
這是一個預留位置，表示可以指定多個 CONTAINS 搜尋條件及其中的詞彙。
備註
全文檢索述詞與函數會在 FROM 述詞中隱含的單一資料表上處理。若要在多個資料表上進行搜尋，使用 FROM 子句中聯結的資料表，在兩個或多個資料表之產品的結果集上進行搜尋。
如果相容層級低於 70，就不會將 CONTAINS 視為關鍵字。如需詳細資訊，請參閱＜sp_dbcmptlevel (Transact-SQL)＞。
當資料庫相容性層級設定為 100 時，OUTPUT 子句中不允許全文檢索述詞。
LIKE 與全文檢索搜尋的比較
相較於全文檢索搜尋，LIKETransact-SQL 述詞只能針對字元模式運作。您也無法使用 LIKE 述詞來查詢格式化的二進位資料。此外，針對大量非結構化文字資料執行 LIKE 查詢的速度會比針對相同資料執行對等全文檢索查詢的速度要慢很多。對於數百萬列的資料，使用 LIKE 查詢時可能要好幾分鐘才能傳回搜尋結果，但是使用全文檢索查詢時可能只要幾秒鐘的時間 (視傳回的資料列數目而定)。
範例
A. 搭配 <simple_term> 使用 CONTAINS
下列範例會尋找價格是 $80.99 且含有 "Mountain" 這個單字的所有產品。
USE AdventureWorks;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
AND CONTAINS(Name, 'Mountain');
GO
B. 使用 CONTAINS 和 <simple_term> 中的片語
下列範例會傳回含有 "Mountain" 或 "Road" 片語的所有產品。
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Mountain" OR "Road" ')
GO
C. 搭配 <prefix_term> 使用 CONTAINS
下列範例會傳回所有至少有一個字的開頭是 Name 資料行中之前置詞鏈結的產品名稱。
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
GO
D. 搭配 <prefix_term> 使用 CONTAINS 和 OR
下列範例會傳回所有包含前置詞為 "chain" 或 "full" 的字串之類別目錄描述。
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO
E. 搭配 <proximity_term> 使用 CONTAINS
下列範例會傳回名稱中 performance 這個字附近有 bike 這個字的所有產品名稱。
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');
GO
F. 搭配 <generation_term> 使用 CONTAINS
下列範例會搜尋所有含 ride 一字下列各種形式的產品：riding、ridden 等等。
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');
GO
G. 搭配 <weighted_term> 使用 CONTAINS
下列範例會搜尋所有包含 performance、comfortable 或 smooth 等字的產品名稱，且每個字都各有不同的加權。
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'ISABOUT (performance weight (.8),
comfortable weight (.4), smooth weight (.2) )' );
GO
H. 搭配變數使用 CONTAINS
下列範例會利用變數來取代特定搜尋詞彙。
USE AdventureWorks;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'Performance'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @SearchWord);
GO
I. 使用 CONTAINS 搭配邏輯運算子 (AND)
下列範例會使用 AdventureWorks 資料庫的 ProductDescription 資料表。此查詢會使用 CONTAINS 述詞來搜尋描述識別碼不等於 5，而且描述同時包含 "Aluminum" 與 "spindle" 這兩個單字的描述。搜尋條件會使用 AND 布林運算子。
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');
GO
J. 使用 CONTAINS 來確認資料列插入
下列範例會在 SELECT 子查詢中使用 CONTAINS。使用 AdventureWorks 資料庫時，查詢會取得 ProductReview 資料表中特定週期之所有註解的註解值。搜尋條件會使用 AND 布林運算子。
USE AdventureWorks;
GO
INSERT INTO Production.ProductReview
(ProductID, ReviewerName, EmailAddress, Rating, Comments)
VALUES
(780, 'John Smith', 'john@fourthcoffee.com', 5,
'The Mountain-200 Silver from AdventureWorks 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 r
JOIN Production.Product p
ON
r.ProductID = p.ProductID
AND r.ProductID = (SELECT ProductID
FROM Production.ProductReview
WHERE CONTAINS (Comments,
' AdventureWorks AND
Redmond AND
"Mountain-200 Silver" '))
GO
請參閱