Interroger avec la requête Full-Text

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

Vue d’ensemble des prédicats Full-Text (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 pour déterminer si une ligne donnée correspond à la requête de texte intégral. Les lignes correspondantes sont restitué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 l’un des autres prédicats Transact-SQL, tels que LIKE et BETWEEN.

Lorsque vous utilisez CONTAINS ou FREETEXT, vous pouvez spécifier une seule colonne, une liste de colonnes ou toutes les colonnes de la table à rechercher. Si vous le souhaitez, vous pouvez spécifier la langue dont les ressources seront utilisées par une requête de texte intégral pour la segmentation et la racine des mots, les scans dans le dictionnaire des synonymes et la suppression des mots vides.

CONTAINS et FREETEXT sont utiles pour différents types de correspondances, comme suit :

• Utilisez CONTAINS (ou CONTAINSTABLE) pour des correspondances précises ou approximatives (moins précises) aux mots et expressions uniques, à la proximité des mots dans une certaine distance d’un autre ou à des correspondances pondérées. Lorsque vous utilisez CONTAINS, vous devez spécifier au moins une condition de recherche qui spécifie le texte que vous recherchez et les conditions qui déterminent les correspondances.

  Vous pouvez utiliser l’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 correspondre à la signification, mais pas à la formulation exacte, des mots, expressions ou phrases spécifiés (chaîne de texte libre). Les correspondances sont générées si un terme ou une forme de terme est trouvé dans l’index de 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 des colonnes indexées en texte intégral des tables cibles sur 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

Un. Utilisation de CONTAINS avec <simple_term>

L’exemple suivant recherche tous les produits dont le prix $80.99 contient le mot "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 Full-Text (CONTAINSTABLE et FREETEXTTABLE)

Les fonctions CONTAINSTABLE et FREETEXTTABLE sont référencées comme un nom de table standard dans la clause FROM d’une instruction SELECT. Ils retournent une table contenant zéro, une ou plusieurs lignes qui correspondent à la requête de texte intégral. La table retournée contient uniquement des 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 :

• colonne clé

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

• Colonne RANK

  La colonne RANK retourne une valeur de classement pour chaque ligne qui indique comment la ligne correspond bien aux critères de sélection. Plus la valeur de classement du texte ou du document d’une ligne est élevée, plus la ligne est pertinente pour la requête de texte intégral donnée. Notez que différentes lignes peuvent être classées de façon identique. 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 recherche avec RANK.

Lorsque vous utilisez l’une de ces fonctions, vous devez spécifier la table de base à rechercher en texte intégral. Comme pour les prédicats, vous pouvez spécifier une seule colonne, une liste de colonnes ou toutes les colonnes de la table à rechercher, et éventuellement, la langue dont les ressources seront utilisées par une requête en texte intégral.

CONTAINSTABLE est utile pour les mêmes types de correspondances que CONTAINS, et FREETEXTTABLE est utile pour les mêmes types de correspondances que FREETEXT. Pour plus d’informations, consultez Vue d’ensemble des prédicats Full-Text (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 des lignes retournées avec les lignes de la table de base SQL Server.

En règle générale, le résultat de CONTAINSTABLE ou FREETEXTTABLE doit être joint à la table de base. Dans ce cas, vous devez connaître le nom de colonne clé unique. Cette colonne, qui se produit dans chaque table avec texte intégral, est utilisée pour appliquer des lignes uniques pour la table (colonne unique**clé). Pour plus d’informations, consultez Gérer les index Full-Text.

Exemples

A. 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 « léger ». 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 suivant étend une requête FREETEXTTABLE pour retourner les lignes les plus classées en premier et 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 une extension de la même requête qui retourne uniquement des lignes avec une valeur de classement de 10 ou supérieure :

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 : ET, OU, ET NON pour effectuer des opérations logiques. Vous pouvez utiliser AND, par exemple, pour rechercher des lignes qui contiennent à la fois « latte » et « Bagel de style New York ». Vous pouvez utiliser AND NOT, par exemple, pour rechercher les lignes qui contiennent « bagel », mais ne contiennent pas de « fromage crème ».

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 La 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 AND Boolean.

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

Considérations supplémentaires relatives aux requêtes Full-Text

Lorsque vous écrivez des requêtes en texte intégral, tenez également compte des éléments suivants :

• Option LANGUE

  De nombreux termes de requête dépendent fortement du comportement du séparateur de mots. Pour vous assurer que vous utilisez le bon analyseur de mots (et le fichier de dictionnaire des synonymes), 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 Full-Text.

• Mots vides

  Lors de la définition d'une requête en texte intégral, le moteur Full-Text ignore les mots vides (également appelés mots parasites) des critères de recherche. Les mots vides sont des mots tels que « a », « et », « is » ou « the », qui peuvent se produire fréquemment, mais qui ne sont généralement pas utiles lors de la recherche de texte particulier. 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 omis 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.

• Le 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 sont insensibles à la casse. Toutefois, en japonais, il existe plusieurs orthographies phonétiques dans lesquelles le concept de normalisation orthographique est semblable à l’insensitivité de cas (par exemple, kana = insensitivité). Ce type de normalisation orthographique n’est pas pris en charge.

Requête des colonnes varbinary(max) et xml

Si une varbinary(max)colonne ou une xmlvarbinarycolonne est indexée en texte intégral, elle peut être interrogée à l’aide des prédicats de texte intégral (CONTAINS et FREETEXT) et des fonctions (CONTAINSTABLE et FREETEXTTABLE), comme toute autre colonne indexée en texte intégral.

varbinary(max) ou varbinary data

Une seule 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 opérationnel. Le type de document de chaque document est identifié par l’extension de fichier du document. 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 de Full-Text peut tirer parti des filtres existants installés dans le système d’exploitation. Avant de pouvoir utiliser des filtres de système d’exploitation, des segmentateurs de mots 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 varbinary(max) colonne, le moteur de Full-Text a besoin d’accéder aux extensions de fichier des documents de la varbinary(max) colonne. Ces informations doivent être stockées dans une colonne de table, appelée colonne de type, qui doit être associée à la varbinary(max) colonne dans l’index de recherche en texte intégral. Lors de l’indexation d’un document, le moteur de Full-Text utilise l’extension de fichier dans la colonne de type pour identifier le filtre à utiliser.

données xml

Une xml colonne de type de données stocke uniquement les documents et fragments XML, et seul le filtre XML est utilisé pour les documents. Par conséquent, une colonne de type n’est pas nécessaire. Sur les colonnes xml, l’index de recherche en texte intégral indexe le contenu des éléments XML, mais ignore le balisage XML. Les valeurs d’attribut sont indexées en texte intégral, sauf s’il s’agit de valeurs numériques. Les balises d’élément sont utilisées comme limites de jeton. Des documents et fragments XML ou HTML bien formés contenant plusieurs langues sont pris en charge.

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

Formulaires de termes de requête pris en charge

Cette section récapitule le soutien apporté pour chaque type de requête par les prédicats de texte intégral et les fonctions d’ensemble de lignes.

Recherche de mots ou d’expressions spécifiques (terme simple)

Vous pouvez utiliser CONTAINS, CONTAINSTABLE, FREETEXT ou FREETEXTTABLE pour rechercher une table pour une expression spécifique. Par exemple, si vous souhaitez rechercher la ProductReview table dans la base de données AdventureWorks2012 pour rechercher 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, dans ce cas « courbe d’apprentissage », peut être assez complexe et peut être composée d’un ou plusieurs termes

Exécution de recherches de préfixes (terme de préfixe)

Vous pouvez utiliser CONTAINS ou CONTAINSTABLE pour rechercher des mots ou des expressions avec un préfixe spécifié. Toutes les entrées de la colonne qui contiennent du texte commençant par le préfixe spécifié sont retournées. Par exemple, pour 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 le texte qui correspond au texte spécifié avant que l’astérisque (*) soit retourné. 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 terme de préfixe est une expression, chaque jeton composant l’expression est considéré comme un terme de préfixe distinct. Toutes les lignes qui ont des mots commençant par les termes de préfixe sont retournées. Par exemple, le terme préfixe « pain léger* » permettra de trouver des lignes contenant les mots « pain léger » ou « légèrement pané », mais il ne retournera pas « pain légèrement grillé ».

Recherche de formes inflectionnelles d’un mot spécifique (terme de génération)

Vous pouvez utiliser CONTAINS, CONTAINSTABLE, FREETEXT ou FREETEXTTABLE pour rechercher toutes les différentes tensions et conjugations d’un verbe ou les formes singulières et plurielles d’un nom (recherche inflectionnelle) ou pour les formes synonymes d’un mot spécifique (recherche de dictionnaire des synonymes).

L’exemple suivant recherche toute forme de « pied » (« pied », « pieds », etc.) dans la colonne Comments de la table ProductReview de 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 ou d’expressions à l’aide de valeurs pondérées (terme pondéré)

Vous pouvez utiliser CONTAINSTABLE pour rechercher des mots ou des expressions et spécifier une valeur de pondération. Le poids, mesuré sous la forme d’un nombre compris entre 0,0 et 1,0, indique l’importance de chaque mot et expression dans un ensemble de mots et d’expressions. Un poids de 0,0 est le plus bas, et un poids de 1,0 est le plus élevé.

L'exemple suivant montre une requête qui recherche toutes les adresses client, en utilisant des pondérations, où les textes débutant par la chaîne « Bay » contiennent soit « Street », soit « View ». Les résultats donnent un rang supérieur à ces lignes qui contiennent plus de mots 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é conjointement avec n’importe quel terme simple, terme de préfixe, terme de génération ou terme de proximité.

Affichage du résultat de la tokenisation d'un module de segmentation de mots, du dictionnaire des synonymes et de listes d'arrêts combinées.

Après avoir appliqué un analyseur de mots donné, un thesaurus et une liste de mots vides à une entrée de chaîne de requête, vous pouvez afficher le résultat de la tokenisation à 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

CONTIENT (Transact-SQL)
CONTAINSTABLE (Transact-SQL)
FREETEXT (Transact-SQL)
FREETEXTTABLE (Transact-SQL)
Créer des requêtes de recherche Full-Text (Outils de base de données visuels)
Améliorer les performances des requêtes Full-Text