CONTAINSTABLE (Transact-SQL)

Article
05/23/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

Retourne une table composée de zéros, d'une ou de plusieurs ligne(s) pour les colonnes contenant des correspondances exactes ou floues (moins précises) de mots simples ou d'expressions, la proximité de mots à une certaine distance les uns des autres ou des correspondances pondérées. CONTAINSTABLE est utilisé dans la clause FROM d’une instruction Transact-SQL SELECT et est référencé comme s’il s’agissait d’un nom de table standard. Il effectue une SQL Server recherche en texte intégral sur des colonnes indexées de texte intégral contenant des types de données basés sur des caractères.

CONTAINSTABLE est utile pour les mêmes types de correspondances que le prédicat CONTAINS et utilise les mêmes conditions de recherche que CONTAINS.

Toutefois, contrairement à CONTAINS, les requêtes utilisant CONTAINSTABLE retournent une valeur de classement de pertinence (RANK) et une clé de texte intégral (KEY) pour chaque ligne. Pour plus d’informations sur les sortes de recherches en texte intégral prises en charge par SQL Server, consultez Exécuter une requête avec une recherche en texte intégral.

Conventions de la syntaxe Transact-SQL

Syntaxe

  
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 ]   
    )

Arguments

table
Nom d'une table qui a été indexée en texte intégral. table peut être un nom d’objet de base de données en un, deux, trois ou quatre parties. Lors de l'interrogation d'une vue, une seule table de base indexée en texte intégral peut être impliquée.

la table ne peut pas spécifier un nom de serveur et ne peut pas être utilisée dans les requêtes sur des serveurs liés.

column_name
Nom d'une ou de plusieurs colonnes indexées pour la recherche en texte intégral. Les colonnes peuvent être de type char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary ou varbinary(max) .

column_list
Indique que plusieurs colonnes, délimitées par des virgules, peuvent être spécifiées. column_list doit être mis entre parenthèses. Une seule et même langue doit être utilisée dans toutes les colonnes de column_list, sauf si language_term est spécifié.

*
Spécifie que toutes les colonnes indexées de texte intégral dans la table doivent être utilisées pour rechercher la condition de recherche donnée. Une seule et même langue doit être utilisée dans toutes les colonnes de la table, sauf si language_term est spécifié.

LANGUAGE language_term
Langue dont les ressources seront utilisées pour la suppression de mots cassant, de la racine et du dictionnaire des synonymes et de noise-word (ou stopword) dans le cadre de la requête. Ce paramètre est facultatif et peut être spécifié sous la forme d'une chaîne, d'un entier ou d'une valeur hexadécimale correspondant à l'identificateur de paramètres régionaux (LCID) d'une langue. Si une langue est définie avec language_term, elle est appliquée à tous les éléments de la condition de recherche. Si aucune valeur n'est définie, la langue du texte intégral de la colonne est utilisée.

Si des documents de langues différentes sont stockés ensemble en tant qu'objets blob dans une colonne unique, l'identificateur de paramètres régionaux (LCID) d'un document donné détermine la langue utilisée pour l'indexation de son contenu. Quand une requête est effectuée sur la colonne, la spécification de LANGUAGE**language_term augmente la probabilité d’une meilleure correspondance.

Lorsqu’il est spécifié sous forme de chaîne, language_term correspond à la valeur de colonne alias dans la vue de compatibilité sys.syslanguages . La chaîne doit être placée entre guillemets simples, comme dans 'language_term'. Quand il est spécifié sous la forme d’un entier, language_term est le LCID qui identifie la langue. Quand il est spécifié sous la forme d’une valeur hexadécimale, language_term est 0x suivi de la valeur hexadécimale du LCID. La valeur hexadécimale ne doit pas dépasser huit caractères, y compris les zéros non significatifs.

Si la valeur est au format DBCS (jeu de caractères codés sur deux octets), Microsoft SQL Server la convertit en Unicode.

Si le langage spécifié n’est pas valide ou si aucune ressource correspondant à ce langage n’est installée, SQL Server retourne une erreur. Pour utiliser des ressources linguistiques neutres, indiquez 0x0 pour language_term.

Top_n_by_rank
Spécifie que seules les n correspondances les plus classées, dans l’ordre décroissant, sont retournées. S’applique uniquement lorsqu’une valeur entière, n, est spécifiée. Si top_n_by_rank est associé à d’autres paramètres, la requête peut retourner moins de lignes que le nombre de lignes correspondant effectivement à tous les prédicats. top_n_by_rank vous permet d’augmenter les performances des requêtes en rappelant uniquement les résultats les plus pertinents.

<contains_search_condition>
Spécifie le texte à rechercher dans column_name et les conditions de correspondance à remplir. Pour plus d’informations sur les conditions de recherche, consultez CONTAINS (Transact-SQL).

Remarques

Les prédicats et les fonctions de texte intégral s'appliquent à une table unique, ce qui est implicite dans le prédicat FROM. Pour effectuer des recherches sur plusieurs tables, utilisez une table jointe dans votre clause FROM afin de baser votre recherche sur un jeu de résultats qui est le produit de deux tables ou plus.

La table retournée a une colonne nommée KEY qui contient des valeurs de clé de texte intégral. Chaque table indexée en texte intégral a une colonne dont les valeurs sont garanties comme uniques, et les valeurs retournées dans la colonne KEY sont les valeurs de clé de texte intégral des lignes qui correspondent aux critères de sélection spécifiés dans la condition de recherche contient. La propriété TableFulltextKeyColumn , obtenue à partir de la fonction OBJECTPROPERTYEX, fournit l’identité de cette colonne de clé unique. Pour obtenir l’ID de la colonne associée à la clé de texte intégral de l’index de texte intégral, utilisez sys.fulltext_indexes. Pour plus d’informations, consultez sys.fulltext_indexes (Transact-SQL).

Pour obtenir les lignes qui vous intéressent dans la table originale, spécifiez une jointure avec les lignes CONTAINSTABLE. La clause FROM d'une instruction SELECT utilisant la fonction CONTAINSTABLE se présente généralement comme suit :

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];

La table produite par CONTAINSTABLE inclut une colonne nommée RANK. La colonne RANK est une valeur (comprise entre 0 et 1 000) pour chaque ligne indiquant dans quelle mesure une ligne correspond aux critères de sélection. Cette valeur de classement est généralement utilisée de l'une des manières suivantes dans l'instruction SELECT :

dans la clause ORDER BY, pour que les lignes ayant le degré de correspondance le plus élevé soient retournées en premier dans la table ;
dans la liste de sélection, pour indiquer la valeur de classement affectée à chaque ligne.

Autorisations

Les autorisations d'exécution sont disponibles uniquement aux utilisateurs bénéficiant des privilèges SELECT appropriés sur la table ou sur les colonnes de la table référencée.

Exemples

R. Exemple simple

L’exemple suivant crée et remplit une table simple de deux colonnes, répertoriant 3 comtés et les couleurs dans leurs indicateurs. Il crée et remplit un catalogue de texte intégral et un index sur la table. Ensuite, la syntaxe CONTAINSTABLE est illustrée. Cet exemple montre comment la valeur de classement augmente lorsque la valeur de recherche est remplie plusieurs fois. Dans la dernière requête, la Tanzanie qui contient à la fois le vert et le noir a un rang supérieur à celui de l’Italie qui ne contient qu’une seule des couleurs interrogées.

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. Retour de valeurs de classement

L'exemple suivant recherche tous les noms de produits contenant les mots « frame », « wheel » ou « tire ». Une pondération différente est affectée à chacun des mots. Le degré de correspondance (valeur de classement) est indiqué pour chaque ligne retournée correspondant à ces critères de recherche. Les lignes ayant le degré de correspondance le plus élevé sont retournées en premier.

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. Retour de valeurs de classement supérieures à une valeur spécifiée


S’applique à : SQL Server 2012 (11.x) et versions ultérieures.

L'exemple suivant utilise NEAR pour rechercher « bracket » et « reflector » à proximité l'un de l'autre dans la table Production.Document. Seules les lignes dont la valeur de classement est supérieure ou supérieure à 50 sont retournées.

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

Notes

Si une requête de texte intégral ne spécifie pas d'entier comme distance maximale, un document qui contient uniquement des correspondances dont l'espacement est supérieur à 100 termes logiques ne satisfera pas les exigences requises par NEAR, et son classement sera de 0.

D. Retour des 5 meilleurs résultats à l'aide de top_n_by_rank

L'exemple suivant retourne la description des 5 premiers produits dont la colonne Description contient les mots « aluminum » à proximité du mot « light » ou « lightweight ».

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. Utilisation de l'argument LANGUAGE

L'exemple suivant illustre l'utilisation de l'argument 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