CONTAINSTABLE (Transact-SQL)

[アーティクル]
05/23/2023

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

1 つの単語や語句に対する正確な一致またはあいまい (精度の低い) 一致、互いに一定の距離内の単語の近接性、重み付けされた一致を含む列の 0 行、1 行、または複数行のテーブルを返します。 CONTAINSTABLE は、Transact-SQL SELECT ステートメントの FROM 句で使用され、通常のテーブル名であるかのように参照されます。文字ベースのデータ型を含むフルテキストインデックス付き列に対して SQL Server フルテキスト検索を実行します。

CONTAINSTABLE は、 CONTAINS 述語と同じ種類の一致に役立ち CONTAINS と同じ検索条件を使用します。

ただし、CONTAINS とは異なり、CONTAINSTABLE を使用するクエリでは、各行の関連性ランク付け値 (RANK) とフルテキストキー (KEY) が返されます。 SQL Server でサポートされているフルテキスト検索の形式については、「フルテキスト検索でのクエリ」を参照してください。

Transact-SQL 構文表記規則

構文

  
CONTAINSTABLE   
( table , { column_name | ( column_list ) | * } , ' <contains_search_condition> '   
     [ , LANGUAGE language_term]   
  [ , top_n_by_rank ]   
)   
  
<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 ]   
    )

引数

テーブル
フルテキストインデックスが作成されたテーブルの名前を指定します。 table は、1、2、3、または 4 部構成のデータベースオブジェクト名にすることができます。ビューに対してクエリを実行する場合は、フルテキストインデックスが作成されたベーステーブルを 1 つだけ指定できます。

table はサーバー名を指定できず、リンクサーバーに対するクエリでは使用できません。

column_name
フルテキスト検索用にインデックスが作成される 1 つ以上の列の名前を指定します。列には、char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary、varbinary(max) のいずれかの型を指定できます。

column_list
コンマ区切りで複数の列を指定できます。 column_list は、かっこで囲む必要があります。 language_term を指定しない場合、column_list で指定するすべての列の言語は同じにする必要があります。

*
table内のすべてのフルテキストインデックス列を使用して、指定された検索条件を検索するように指定します。 language_term を指定しない場合、テーブルのすべての列の言語は同じである必要があります。

LANGUAGE language_term
クエリの一部として単語区切り、ステミング、類義語辞典とノイズワード (またはストップワード) の削除にリソースを使用する言語です。このパラメーターは省略可能で、言語のロケール識別子 (LCID) に対応する文字列、整数、または 16 進数の値を指定できます。 language_term を指定した場合、その言語は検索条件のすべての要素に適用されます。値を指定しなかった場合は、列のフルテキストの言語が使用されます。

1 つの列に言語の異なる複数のドキュメントが BLOB (Binary Large Object) として格納されている場合、そのインデックスの作成に使用される言語は、そのドキュメントのロケール識別子 (LCID) によって決まります。そのような列に対してクエリを実行する場合は、LANGUAGE**language_term を指定すると検索結果の一致率が高まります。

文字列として指定した場合、language_termは sys.syslanguages 互換性ビューの alias 列の値に対応します。文字列の場合は、'language_term' のように引用符 (') で囲む必要があります。 language_term を整数で指定する場合は、その言語を表す実際の LCID を指定します。 language_term を 16 進数の値で指定する場合は、「0x」の後に LCID の 16 進数の値を指定します。 16 進数の値は、先頭の 0 を含め、8 桁以内で指定してください。

値を 2 バイト文字セット (DBCS) の形式で指定すると、Microsoft SQL Server で Unicode に変換されます。

指定した言語が無効であるか、その言語に該当するリソースがインストールされていない場合は、SQL Server によりエラーが返されます。ニュートラル言語リソースを使用するには、language_term に「0x0」を指定してください。

top_n_by_rank
降順で n 最も高いランクの一致のみが返されるように指定します。整数値 ( n) が指定されている場合にのみ適用されます。 top_n_by_rank を他のパラメーターと組み合わせた場合、クエリから返される行数は、実際にすべての述語に一致する行数より少なくなります。 top_n_by_rank では、最も関連性の高いヒットのみを呼び出すことで、クエリのパフォーマンスを向上させることができます。

<contains_search_condition>
column_name で検索するテキストと、その一致条件を指定します。検索条件の詳細については、「 CONTAINS (Transact-SQL)」を参照してください。

解説

フルテキストの述語と関数の対象は、FROM 述語で示される 1 つのテーブルです。複数のテーブルを検索するには、FROM 句で結合テーブルを使用して、複数のテーブルが組み合わされた結果セットを検索します。

返されるテーブルには、フルテキストキー値を含む KEY という名前の列があります。各フルテキストインデックステーブルには、値が一意であることが保証される列があり、 KEY 列に返される値は、contains 検索条件で指定された選択条件に一致する行のフルテキストキー値です。 OBJECTPROPERTYEX 関数から取得した TableFulltextKeyColumn プロパティは、この一意のキー列の ID を提供します。フルテキストインデックスのフルテキストキーに関連付けられている列の ID を取得するには、 sys.fulltext_indexesを使用します。詳細については、「 sys.fulltext_indexes (Transact-SQL)」を参照してください。

元のテーブルから目的の行を取得するには、CONTAINSTABLE 行との結合を指定してください。 CONTAINSTABLE を使用する場合、通常は次の形式で FROM 句を SELECT ステートメントに指定します。

SELECT select_list  
FROM table AS FT_TBL INNER JOIN  
   CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL  
   ON FT_TBL.unique_key_column = KEY_TBL.[KEY];

CONTAINSTABLE によって生成されるテーブルには、 RANK という名前の列が含まれています。 RANK列は、行が選択基準とどの程度一致したかを示す各行の値 (0 から 1000 まで) です。このランク値は、通常、SELECT ステートメントで次のいずれかの方法で使用されます。

ORDER BY 句で、テーブルの最初の行として最上位の行を返します。
選択リストで、各行に割り当てられているランク値を表示します。

アクセス許可

実行アクセス許可は、テーブルまたは参照先テーブルの列に対する適切な SELECT 権限を持つユーザーのみが使用できます。

例

A. 簡単な例

次の例では、2 つの列の単純なテーブルを作成して設定します。3 つの郡とそのフラグの色が一覧表示されます。テーブルにフルテキストカタログとインデックスを作成して設定します。次に、 CONTAINSTABLE 構文を示します。この例では、検索値が複数回満たされたときにランク値が高くなる方法を示します。最後のクエリでは、緑と黒の両方を含むタンザニアは、クエリされた色の 1 つだけを含むイタリアよりも高いランクを持っています。

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));  
CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);  
INSERT Flags VALUES ('France', 'Blue and White and Red');  
INSERT Flags VALUES ('Italy', 'Green and White and Red');  
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');  
SELECT * FROM Flags;  
GO  
  
CREATE FULLTEXT CATALOG TestFTCat;  
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;  
GO   
  
SELECT * FROM Flags;  
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green') ORDER BY RANK DESC;  
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green or Black') ORDER BY RANK DESC;

B. 順位値を返す

次の例では、"frame"、"wheel"、または "tire" という単語を含むすべての製品名を検索し、各単語に異なる重みを指定します。これらの検索条件に一致する返される行ごとに、一致の相対的な近さ (ランク付け値) が表示されます。また、最も順位値の高い行を最初に返します。

USE AdventureWorks2022;  
GO  
  
SELECT FT_TBL.Name, KEY_TBL.RANK  
    FROM Production.Product AS FT_TBL   
        INNER JOIN CONTAINSTABLE(Production.Product, Name,   
        'ISABOUT (frame WEIGHT (.8),   
        wheel WEIGHT (.4), tire WEIGHT (.2) )' ) AS KEY_TBL  
            ON FT_TBL.ProductID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC;  
GO

C: 指定した値より大きいランク値を返す


適用対象: SQL Server 2012 (11.x) 以降。

次の例では、NEAR を使用して、bracket テーブルで、相互に近接する "reflector" および "Production.Document" を検索します。また、順位値が 50 以上の行だけを返します。

USE AdventureWorks2022  
GO  
  
SELECT DocumentNode, Title, DocumentSummary  
FROM Production.Document AS DocTable   
INNER JOIN CONTAINSTABLE(Production.Document, Document,  
  'NEAR(bracket, reflector)' ) AS KEY_TBL  
  ON DocTable.DocumentNode = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK > 50  
ORDER BY KEY_TBL.RANK DESC;  
GO

Note

フルテキストクエリで最大距離として整数が指定されていない場合、ギャップが 100 を超える論理用語のヒットのみを含むドキュメントは NEAR 要件を満たせず、そのランク付けは 0 になります。

D. top_n_by_rank を使用して上位 5 個の結果を返す

次の例では、Description 列内で "light" または "lightweight" という単語の近くに "aluminum" という語句を含んでいる、上位 5 種の製品の説明を返します。

USE AdventureWorks2022;  
GO  
  
SELECT FT_TBL.ProductDescriptionID,  
   FT_TBL.Description,   
   KEY_TBL.RANK  
FROM Production.ProductDescription AS FT_TBL INNER JOIN  
   CONTAINSTABLE (Production.ProductDescription,  
      Description,   
      '(light NEAR aluminum) OR  
      (lightweight NEAR aluminum)',  
      5  
   ) AS KEY_TBL  
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY];  
GO

GO

E. LANGUAGE 引数を指定する

次の例は、 LANGUAGE 引数の使用を示しています。

USE AdventureWorks2022;  
GO  
  
SELECT FT_TBL.ProductDescriptionID,  
   FT_TBL.Description,   
   KEY_TBL.RANK  
FROM Production.ProductDescription AS FT_TBL INNER JOIN  
   CONTAINSTABLE (Production.ProductDescription,  
      Description,   
      '(light NEAR aluminum) OR  
      (lightweight NEAR aluminum)',  
      LANGUAGE N'English',  
      5  
   ) AS KEY_TBL  
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY];  
GO

Note

language language_term 引数は、 top_n_by_rankを使用するために必要ありません。

参照

RANK を使用して検索結果を制限する方法
 フルテキスト検索でのクエリ
 フルテキスト検索クエリの作成 (Visual Database Tools)
CONTAINS (Transact-SQL)
フルテキスト検索でのクエリ
 SELECT (Transact-SQL)
FROM (Transact-SQL)

次の方法で共有