Exécuter une requête avec une recherche en texte intégral

Pour définir des recherches en texte intégral, SQL Server requêtes de texte intégral utilisent les prédicats de texte intégral (CONTAINS et FREETEXT) et les fonctions (CONTAINSTABLE et FREETEXTTABLE. Ceux-ci prennent en charge une syntaxe Transact-SQL riche qui prend en charge diverses formes de termes de requête. Pour écrire des requêtes de texte intégral, vous devez apprendre quand et comment utiliser ces prédicats et ces fonctions.

Vue d'ensemble des prédicats de texte intégral (CONTAINS et FREETEXT)

Les prédicats CONTAINS et FREETEXT retournent une valeur TRUE ou FALSE. Ils peuvent être utilisés uniquement pour spécifier des critères de sélection permettant de déterminer si une ligne donnée correspond à la requête de texte intégral. Les lignes correspondantes sont retournées dans le jeu de résultats. CONTAINS et FREETEXT sont spécifiés dans la clause WHERE ou HAVING d'une instruction SELECT. Ils peuvent être combinés avec n’importe lequel des autres prédicats Transact-SQL, tels que LIKE et BETWEEN.

Lorsque vous utilisez CONTAINS ou FREETEXT, vous pouvez spécifier une colonne unique, une liste de colonnes, ou toutes les colonnes de la table sur laquelle porte la recherche. Vous pouvez éventuellement spécifier la langue dont les ressources seront utilisées par une requête de texte intégral donnée pour l'analyse lexicale et la recherche de radical, les consultations de dictionnaire des synonymes et la suppression de mots parasites.

Les prédicats CONTAINS et FREETEXT sont utiles pour différents types de correspondance, comme expliqué ci-après.

• Utilisez CONTAINS (ou CONTAINSTABLE) pour des concordances précises ou approximatives (moins précises) avec des mots isolés ou des expressions, des répétitions ou des concordances pondérées. Lorsque vous utilisez CONTAINS, vous devez spécifier au moins une condition de recherche qui indique le texte que vous recherchez ainsi que les conditions qui déterminent les correspondances.

  Vous pouvez utiliser une opération logique entre les conditions de recherche. Pour plus d’informations, consultez Utilisation d’opérateurs booléens-AND, OR, AND NOT (dans CONTAINS et CONTAINSTABLE) plus loin dans cette rubrique.

• Utilisez FREETEXT (ou FREETEXTTABLE) pour rechercher des correspondances de signification, et non pas le libellé exact, des mots, expressions ou phrases spécifiés ( chaîne en texte libre). Des correspondances sont générées si un terme ou une forme de quelque terme que ce soit est trouvé dans l'index de recherche en texte intégral d'une colonne spécifiée.

Vous pouvez utiliser un nom en quatre parties dans le prédicat CONTAINS ou FREETEXT pour interroger les colonnes indexées de texte intégral des tables cibles d'un serveur lié. Pour préparer un serveur distant à recevoir des requêtes de texte intégral, créez un index de recherche en texte intégral sur les tables et colonnes cibles du serveur distant, puis ajoutez le serveur distant comme serveur lié.

Exemples

R. Utilisation de CONTAINS avec <simple_term>

L'exemple ci-dessous recherche tous les produits qui contiennent le mot $80.99 et qui coûtent "Mountain".

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

B. Utilisation de FREETEXT pour rechercher des mots contenant les valeurs de caractère spécifiées

L'exemple suivant recherche tous les documents contenant les mots liés à « vital », « safety » et « components ».

USE AdventureWorks2012  
GO  
  
SELECT Title  
FROM Production.Document  
WHERE FREETEXT (Document, 'vital safety components')  
GO  

Vue d'ensemble des fonctions de texte intégral (CONTAINSTABLE et FREETEXTTABLE)

Les fonctions CONTAINSTABLE et FREETEXTTABLE sont référencées comme un nom de table régulier dans la clause FROM d'une instruction SELECT. Ils retournent une table contenant aucune, une ou plusieurs ligne(s) correspondant à la requête de texte intégral. La table retournée contient uniquement les lignes de la table de base qui correspondent aux critères de sélection spécifiés dans la condition de recherche en texte intégral de la fonction.

Les requêtes utilisant l'une de ces fonctions retournent une valeur de classement de pertinence (RANK) et une clé de texte intégral (KEY) pour chaque ligne, comme suit :

• KEY (colonne)

  La colonne KEY retourne les valeurs uniques des lignes retournées. La colonne KEY peut être utilisée pour spécifier des critères de sélection.

• RANK (colonne)

  La colonne RANK retourne une valeur de classement pour chaque ligne qui indique le degré de correspondance de cette dernière avec les critères de sélection. Plus la valeur de classement du texte ou document d'une ligne est élevée, plus celle-ci est pertinente pour la requête de texte intégral. Notez que différentes lignes peuvent avoir le même classement. Vous pouvez limiter le nombre de correspondances à retourner en spécifiant le paramètre facultatif top_n_by_rank . Pour plus d’informations, consultez Limiter les résultats de la recherche avec RANK.

Lorsque vous utilisez l'une ou l'autre de ces fonctions, vous devez spécifier la table de base sur laquelle doit porter la recherche en texte intégral. Comme avec les prédicats, vous pouvez spécifier une colonne unique, une liste de colonnes, ou toutes les colonnes de la table sur laquelle doit porter la recherche, et éventuellement, la langue dont les ressources seront utilisées par une requête de texte intégral donnée.

CONTAINSTABLE est utile pour les mêmes types de correspondance que CONTAINS, et FREETEXTTABLE est utile pour les mêmes types de correspondance que FREETEXT. Pour plus d’informations, consultez Vue d’ensemble des prédicats de texte intégral (CONTAINS et FREETEXT), plus haut dans cette rubrique. Lorsque vous exécutez des requêtes qui utilisent les fonctions CONTAINSTABLE et FREETEXTTABLE, vous devez joindre explicitement les lignes retournées avec les lignes de la table de base SQL Server.

En général, le résultat des fonctions CONTAINSTABLE ou FREETEXTTABLE doit être joint avec la table de base. Dans ce cas-là, vous devez connaître le nom de la colonne clé unique. Cette colonne, qui est présente dans toutes les tables activées pour la recherche en texte intégral, garantit l’unicité des lignes de la table (colonne clé**unique). Pour plus d’informations, consultez Gérer les index de recherche en texte intégral.

Exemples

R. Utilisation de CONTAINSTABLE

L’exemple suivant retourne l’ID de description et la description de tous les produits pour lesquels la colonne Description contient le mot « aluminium » près du mot « light » ou du mot « lightweight ». Seules les lignes dont la valeur de classement est 2 ou supérieure sont retournées.

USE AdventureWorks2012  
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)'  
   ) AS KEY_TBL  
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK > 2  
ORDER BY KEY_TBL.RANK DESC;  
GO  

B. Utilisation de FREETEXTTABLE

L'exemple ci-après étend une requête FREETEXTTABLE afin de retourner en premier les lignes dont le niveau de classement est le plus élevé et d'ajouter le classement de chaque ligne à la liste de sélection. Pour spécifier la requête, vous devez savoir que ProductDescriptionID est la colonne clé unique de la ProductDescription table.

USE AdventureWorks2012  
GO  
  
SELECT KEY_TBL.RANK, FT_TBL.Description  
FROM Production.ProductDescription AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(Production.ProductDescription, Description,  
                    'perfect all-around bike') AS KEY_TBL  
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC  
GO  

Voici l'extension de la même requête qui renvoie uniquement les lignes avec une valeur de rang égale ou supérieure à 10 :

USE AdventureWorks2012  
GO  
  
SELECT KEY_TBL.RANK, FT_TBL.Description  
FROM Production.ProductDescription AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(Production.ProductDescription, Description,  
                    'perfect all-around bike') AS KEY_TBL  
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK >= 10  
ORDER BY KEY_TBL.RANK DESC  
GO  

Utilisation d’opérateurs booléens - AND, OR et NOT - dans CONTAINS et CONTAINSTABLE

Le prédicat CONTAINS et la fonction CONTAINSTABLE utilisent les mêmes conditions de recherche. Les deux prennent en charge la combinaison de plusieurs termes de recherche à l’aide d’opérateurs booléens -AND, OR, AND NOT- pour effectuer des opérations logiques. Par exemple, vous pouvez utiliser AND pour rechercher des lignes qui contiennent à la fois « latte » et « New York-style bagel ». Vous pouvez utiliser AND NOT pour rechercher les lignes qui contiennent « bagel » mais qui ne contiennent pas « cream cheese ».

Pour plus d’informations sur la combinaison de CONTAINS avec d’autres prédicats qui utilisent les opérateurs logiques AND, OR et NOT, consultez Condition de recherche (Transact-SQL).

Exemple

L'exemple suivant utilise la table ProductDescription de la base de données AdventureWorks2012. La requête utilise le prédicat CONTAINS pour rechercher des descriptions dans lesquelles l’ID de description n’est pas égal à 5 et la description contient à la fois le mot « Aluminium » et le mot « spindle ». La condition de recherche utilise l’opérateur booléen AND.

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

Considérations supplémentaires pour les requêtes de texte intégral

Lors de l'écriture de requêtes de texte intégral, prenez également en considération les points suivants :

• LANGUAGE (option)

  De nombreux termes de requête dépendent fortement du comportement de l'analyseur lexical. Pour être certain d'utiliser l'analyseur lexical (et le générateur de formes dérivées) et le dictionnaire des synonymes appropriés, nous vous recommandons de spécifier l'option LANGUAGE. Pour plus d’informations, consultez Choisir une langue lors de la création d’un index de recherche en texte intégral.

• Mots vides

  Lorsqu'une requête de texte intégral est définie, le Moteur d'indexation et de recherche en texte intégral supprime les mots vides (également appelés mots parasites) des critères de recherche. Les mots vides sont des mots tels que « un », « et », « est » ou « le » dont les occurrences sont fréquentes mais qui ne sont pas utiles pour une recherche de texte spécifique. Les mots vides sont répertoriés dans une liste de mots vides. Chaque index de recherche en texte intégral est associé à une liste de mots vides spécifique, qui détermine les mots vides à omettre de la requête ou de l'index au moment de l'indexation. Pour plus d’informations, consultez Configurer et gérer les mots vides et listes de mots vides pour la recherche en texte intégral.

• Dictionnaire des synonymes

  Les requêtes FREETEXT et FREETEXTTABLE utilisent le dictionnaire des synonymes par défaut. CONTAINS et CONTAINSTABLE prennent en charge un argument THESAURUS facultatif.

• Respect de la casse

  Les requêtes de recherche en texte intégral ne respectent pas la casse. Néanmoins, en ce qui concerne le Japonais, il existe plusieurs orthographes phonétiques pour lesquelles le concept de normalisation orthographique est apparenté au respect de la casse (par exemple kana = non respect). Ce genre de normalisation orthographique n'est pas pris en charge.

Exécution de requêtes sur des colonnes varbinary(max) et xml

Si une colonne varbinary(max), varbinary ou xml est indexée en texte intégral, elle peut faire l'objet d'une requête à l'aide des prédicats de texte intégral (CONTAINS et FREETEXT) et des fonctions de texte intégral (CONTAINSTABLE et FREETEXTTABLE), au même titre que n'importe quelle autre colonne indexée en texte intégral.

Données varbinary(max) ou varbinary

Une même colonne varbinary(max) ou varbinary peut stocker de nombreux types de documents. SQL Server prend en charge tout type de document pour lequel un filtre est installé et disponible dans le système d'exploitation. Le type d'un document est identifié par l'extension de fichier de celui-ci. Par exemple, pour une extension de fichier .doc, la recherche en texte intégral utilise le filtre qui prend en charge les documents Microsoft Word. Pour obtenir la liste des types de documents disponibles, interrogez l’affichage catalogue sys.fulltext_document_types .

Notez que le Moteur d'indexation et de recherche en texte intégral peut bénéficier des filtres installés dans le système d'exploitation. Avant de pouvoir utiliser des filtres de système d'exploitation, des analyseurs lexicaux et des générateurs de formes dérivées, vous devez les charger dans l'instance de serveur, comme suit :

EXEC sp_fulltext_service @action='load_os_resources', @value=1  

Pour créer un index de recherche en texte intégral sur une colonne varbinary(max), le Moteur d'indexation et de recherche en texte intégral a besoin d'accéder aux extensions de fichier des documents dans la colonne varbinary(max). Ces informations doivent être stockées dans une colonne de table, appelée colonne de type, qui doit être associée à la colonne varbinary(max) dans l'index de recherche en texte intégral. Lors de l'indexation d'un document, le Moteur d'indexation et de recherche en texte intégral utilise l'extension de fichier indiquée dans la colonne de type pour identifier le filtre à utiliser.

Données xml

Une colonne de type de données xml stocke uniquement des documents et des fragments XML, et seul le filtre XML est utilisé pour les documents. Par conséquent, une colonne de type est inutile. Sur les colonnes xml, l'index de recherche en texte intégral indexe le contenu des éléments XML, sans prendre en compte les balises XML. Les valeurs d'attributs sont indexées en texte intégral, à moins qu'il ne s'agisse de valeurs numériques. Des balises d'éléments sont utilisées comme limites de jeton. Les fragments et les documents XML ou HTML correctement formés contenant plusieurs langues sont pris en charge.

Pour plus d’informations sur l’interrogation sur une xml colonne, consultez Utiliser la recherche Full-Text avec des colonnes XML.

Formulaires de termes de requête pris en charge

Cette section récapitule la prise en charge fournie pour chaque formulaire de requête par les prédicats de texte intégral et les fonctions d'ensemble de lignes.

Recherche d’un mot ou d’une expression spécifique (terme simple)

Vous pouvez utiliser CONTAINS, CONTAINSTABLE, FREETEXTou FREETEXTTABLE pour rechercher une expression spécifique dans une table. Par exemple, si vous souhaitez effectuer une recherche dans la ProductReview table dans la base de données AdventureWorks2012 pour trouver tous les commentaires sur un produit avec l’expression « courbe d’apprentissage », vous pouvez utiliser le prédicat CONTAINS comme suit :

USE AdventureWorks2012  
GO  
  
SELECT Comments  
FROM Production.ProductReview  
WHERE CONTAINS(Comments, '"learning curve"')  
GO  

La condition de recherche, en l'occurrence « learning curve », peut être complexe et comporter un ou plusieurs termes.

Recherche de préfixes (terme de préfixe)

Vous pouvez utiliser CONTAINS ou CONTAINSTABLE pour rechercher des mots ou des expressions ayant un préfixe que vous spécifiez. Toutes les entrées de la colonne qui contiennent le texte commençant par le préfixe spécifié sont retournées. Par exemple, rechercher toutes les lignes qui contiennent le préfixe top-, comme dans top``ple, top``pinget top. La requête ressemble à ceci :

USE AdventureWorks2012  
GO  
  
SELECT Description, ProductDescriptionID  
FROM Production.ProductDescription  
WHERE CONTAINS (Description, '"top*"' )  
GO  

Tout texte correspondant au texte spécifié avant l'astérisque (*) est renvoyé. Si le texte et l'astérisque ne sont pas délimités par des guillemets doubles, comme dans CONTAINS (DESCRIPTION, 'top*'), la recherche en texte intégral ne considère pas l'astérisque comme un caractère générique.

Lorsque le préfixe est une expression, chaque jeton à l'intérieur de l'expression est considéré comme un préfixe distinct. Toutes les lignes qui possèdent des mots qui commencent par les préfixes seront renvoyés. Par exemple, le préfixe « light bread* » trouve des lignes contenant le texte « light breaded », « lightly breaded » ou « light bread », mais il ne retourne pas « lightly toasted bread ».

Recherche de formes fléchies d'un mot spécifique (forme canonique)

Vous pouvez utiliser CONTAINS, CONTAINSTABLE, FREETEXTou FREETEXTTABLE pour rechercher tous les différents temps et conjugaisons d’un verbe, le pluriel et le singulier d’un nom (recherche des formes fléchies) ou les formes synonymes d’un mot spécifique (recherche dans le dictionnaire des synonymes).

L'exemple suivant recherche toutes les formes de « foot » (« foot », « feet », etc.) dans la colonne Comments de la table ProductReview dans la base de données AdventureWorks .

USE AdventureWorks2012  
GO  
  
SELECT Comments, ReviewerName  
FROM Production.ProductReview  
WHERE CONTAINS (Comments, 'FORMSOF(INFLECTIONAL, "foot")')  
GO  

Recherche de mots et d’expressions à l’aide de valeurs pondérées (termes pondérés)

Vous pouvez utiliser CONTAINSTABLE pour rechercher des mots ou des expressions et spécifier une valeur pondérée. La pondération d'un terme, qui est une mesure numérique comprise entre 0.0 et 1.0, indique l'importance de chaque mot et expression au sein d'un ensemble de mots et d'expressions. L'échelle de pondération oscille entre un minimum de 0.0 et un maximum de 1.0.

L'exemple suivant affiche une requête qui recherche toutes les adresses de client à l'aide de pondérations, où tout texte qui commence par la chaîne « Bay » comporte soit « Street » soit « View ». Les résultats accordent un rang plus élevé aux lignes qui contiennent le plus de mots, parmi ceux spécifiés.

USE AdventureWorks2012  
GO  
  
SELECT AddressLine1, KEY_TBL.RANK   
FROM Person.Address AS Address INNER JOIN  
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",   
         Street WEIGHT(0.9),   
         View WEIGHT(0.1)  
         ) ' ) AS KEY_TBL  
ON Address.AddressID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC  
GO  

Un terme pondéré peut être utilisé avec tout terme simple, terme de préfixe, terme canonique ou terme de proximité.

Affichage du résultat de la segmentation du texte en unités lexicales pour une combinaison d'analyseur lexical, de dictionnaire des synonymes et de liste de mots vides

Après avoir appliqué une combinaison d’analyseur lexical, de dictionnaire des synonymes et de liste de mots vides à une entrée de chaîne de requête, vous pouvez afficher le résultat de la segmentation du texte en unités lexicales à l’aide de la vue de gestion dynamique sys.dm_fts_parser. Pour plus d’informations, consultez sys.dm_fts_parser (Transact-SQL).

Voir aussi

CONTAINS (Transact-SQL)
CONTAINSTABLE (Transact-SQL)
FREETEXT (Transact-SQL)
FREETEXTTABLE (Transact-SQL)
Créer des requêtes de recherche en texte intégral (Visual Database Tools)
Améliorer les performances des requêtes de texte intégral