CONTAINS (Transact-SQL)
文字ベースのデータ型を含む列を検索するために WHERE 句で使用される述語です。単語または語句の完全一致検索やあいまい検索、特定の範囲内で近くにある単語の検索、重み付けした検索などを実行する場合に使用されます。
SQL Server では、CONTAINS または FREETEXT フルテキスト述語で、4 つの要素から構成される名前を使ってリンク サーバーに対しクエリを実行できます。
次に CONTAINS の検索対象を示します。
単語または語句。
単語または語句のプレフィックス。
別の単語の近くにある単語。
他の単語を語形変化して生成した単語。たとえば、drive という単語からは、drives、drove、driving、driven などの単語が生成されます。
別の単語のシノニムになっている単語 (類義語を使用)。たとえば、金属という単語には、アルミニウムやスチールなどのシノニムがあります。
.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
クエリにおいて、単語区切り、語幹検索、類義語の拡張と置換、およびノイズ語 (ストップ ワード) の破棄を行うときに使用する言語を指定します。このパラメータはオプションです。1 つの列に複数の異なる言語のドキュメントが BLOB (Binary Large Object) として格納されている場合、インデックスの作成に使用される言語は、各ドキュメントのロケール識別子 (LCID) によって決まります。そのような列に対してクエリを実行する場合は、LANGUAGE language_term を指定すると検索結果の一致率が高まります。
language_term には、言語の LCID に対応する文字列、整数、または 16 進数の値を指定できます。language_term を指定した場合、その言語は検索条件のすべての要素に適用されます。値を指定しなかった場合は、列のフルテキストの言語が使用されます。
language_term を文字列で指定する場合は、sys.syslanguages (Transact-SQL) 互換性ビューの alias 列の値と同じ値を指定します。文字列の場合は、'language_term' のように単一引用符 (') で囲む必要があります。language_term を整数で指定する場合は、その言語を表す実際の LCID を指定します。language_term を 16 進数の値で指定する場合は、「0x」の後に LCID の 16 進数の値を指定します。16 進数の値は、先頭の 0 を含め、8 桁以内で指定してください。
値を 2 バイト文字セット (DBCS) の形式で指定すると、SQL Server で Unicode に変換されます。
指定した言語が無効であるか、その言語に該当するリソースがインストールされていない場合は、エラーが返されます。ニュートラル言語リソースを使用するには、language_term に「0x0」を指定してください。
<contains_search_condition>
column_name で検索するテキストと、その一致条件を指定します。<contains_search_condition> のデータ型は nvarchar です。入力に他の文字データ型が使用された場合は、暗黙の変換が行われます。次の例では、CONTAINS 述語において、varchar(30) として定義されている変数 @SearchWord が暗黙に変換されます。
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
単語の間をスペースで区切った 1 つ以上の単語です。.gif "注意")
注意一部のアジア言語など、言語の中には、語句を構成するときに単語の間にスペースを挿入しないものがあります。
<simple_term>
単語または語句に対する完全一致を指定します。有効な単純語の例として、"blue berry"、blueberry、および "Microsoft SQL Server" などがあります。語句は二重引用符 ("") で囲みます。語句内の単語は、<contains_search_condition> の指定と同じ順序で、データベース列に含まれている必要があります。単語または語句内の文字の検索では、大文字と小文字は区別されません。フルテキスト インデックス付きの列の a、and、the などのノイズ ワード (ストップ ワード) は、フルテキスト インデックスには格納されません。1 つの単語の検索にノイズ ワードを使用した場合、SQL Server ではクエリにノイズ ワードだけが指定されていることを示すエラーが返されます。SQL Server では、SQL Server の各インスタンスのディレクトリ \Mssql\Binn\FTERef にノイズ ワードの標準リストがあります。区切り記号は無視されます。したがって、CONTAINS(testing, "computer failure") は、"Where is my computer? Failure to find it would be expensive." がある行に一致します。単語区切りの動作の詳細については、「ワード ブレーカとステミング機能」を参照してください。
<prefix_term>
指定のテキストで始まる単語または語句の照合を指定します。プレフィックス語句を二重引用符 ("") で囲み、後ろの二重引用符の前にアスタリスク (*) を挿入すると、アスタリスクの前に指定された文字列で始まるすべてのテキストが照合されます。句は、CONTAINS (column, '"text*"') のように指定してください。アスタリスクは、一致する文字がないか、1 つまたはそれ以上の文字に一致します。その単語または語句を語根とする文字も対象になります。テキストとアスタリスクが二重引用符で区切られていないと、述語が読み取る内容は 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>
検索対象のドキュメントに含まれている必要がある単語または語句の照合を指定します。<proximity_term> では、AND 演算子と同じように、両方の検索語が検索対象のドキュメントに含まれている必要があります。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 | | }
2 つの contains 検索条件の間の論理演算を指定します。AND | &
一致条件として、2 つの contains 検索条件を満たすことを指定します。アンパサンド記号 (&) は、AND キーワードの代わりに使用して、AND 演算子を表すことができます。AND NOT | &!
2 番目の検索条件が存在しないことを、一致条件として指定します。アンパサンドとその次の感嘆符 (&!) は、AND NOT キーワードの代わりに使用して、AND NOT 演算子を表すことができます。OR | |
一致条件として、2 つの contains 検索条件のいずれかを満たすことを指定します。垂直バー記号 (|) は、OR キーワードの代わりに使用して、OR 演算子を表すことができます。<contains_search_condition> 内にかっこで囲まれたグループが含まれる場合は、かっこで囲まれたグループが最初に評価されます。かっこで囲まれたグループを評価した後は、contains 検索条件で使用される論理演算子に対して次の規則が適用されます。
NOT は AND より先に適用されます。
NOT は AND NOT のように、AND の後にだけ指定できます。OR NOT 演算子は使用できません。NOT は最初の条件の前に指定できません。たとえば、CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ) は無効になります。
AND は OR より先に適用されます。
同じタイプの論理演算子 (AND、OR) は結合されるので、任意の順番で適用できます。
- n
複数の CONTAINS 検索条件と、条件内の複数の語を指定できることを示すプレースホルダです。
説明
フルテキストの述語と関数の対象は、FROM 述語で示される 1 つのテーブルです。複数のテーブルを検索するには、FROM 句で結合テーブルを使用して、複数のテーブルが組み合わされた結果セットを検索します。
CONTAINS は、互換性レベルが 70 未満の場合はキーワードとして認識されません。詳細については、「sp_dbcmptlevel (Transact-SQL)」を参照してください。
データベースの互換性レベルが 100 に設定されている場合、OUTPUT 句でフルテキスト述語を使用することはできません。
LIKE とフルテキスト検索の比較
フルテキスト検索とは異なり、LIKETransact-SQL 述語は文字パターンにのみ有効です。また、フォーマットされたバイナリ データのクエリには LIKE 述語を使用できません。さらに、構造化されていない大量のテキスト データに対して LIKE クエリを実行すると、同じデータに対して同等のフルテキスト検索を実行する場合に比べてはるかに時間がかかります。数百万行のテキスト データに対して LIKE クエリを実行すると、結果が得られるまでに数分かかる場合があります。一方、同じデータに対してフルテキスト クエリを実行すると、返される行数にもよりますが、数秒以内で結果を取得できます。
例
A. CONTAINS を <simple_term> と共に使用する
次の例では、"Mountain" という単語を含み、価格が $80.99 であるすべての製品を検索します。
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. CONTAINS を <prefix_term> と共に使用する
次の例では、Name 列の中で、chain というプレフィックスで始まる 1 つ以上の単語が含まれている、すべての製品名を返します。
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
GO
D. CONTAINS および OR を <prefix_term> と共に使用する
次の例では、"chain" または "full" のいずれかのプレフィックスを持つ文字列が含まれている、すべてのカテゴリ説明を返します。
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO
E. CONTAINS を <proximity_term> と共に使用する
次の例では、performance という単語の近くに bike という単語が存在する、すべての製品の名前を返します。
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');
GO
F. CONTAINS を <generation_term> と共に使用する
次の例では、ride を原型とする riding、ridden などの単語が含まれている、すべての製品を検索します。
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');
GO
G. CONTAINS を <weighted_term> と共に使用する
次の例では、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 述語を使用して、説明 ID が 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
関連項目