Consulta com pesquisa de texto completo

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Escreva consultas de texto completo usando os predicados CONTAINS e FREETEXT e as funções com valor de conjunto de linhas CONTAINSTABLE e FREETEXTTABLE com uma instrução SELECT. Este artigo fornece exemplos de cada predicado e função e ajuda você a escolher o melhor a ser usado.

  • Para corresponder palavras e frases, use CONTAINS e CONTAINSTABLE.
  • Para corresponder o significado, mas não as palavras exatas, use FREETEXT e FREETEXTTABLE.

Exemplos de cada predicado e função

Os exemplos a seguir usam o banco de dados de exemplo AdventureWorks. Para encontrar a versão final do AdventureWorks, consulte Bancos de dados e scripts do AdventureWorks para SQL Server 2016 CTP3. Para executar as consultas de exemplo, você também deve configurar a pesquisa de texto completo. Para mais informações, consulte Introdução à pesquisa de texto completo.

Exemplo – CONTAINS

O exemplo a seguir localiza todos os produtos com um preço de $80.99 contendo a palavra "Mountain":

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

Exemplo – FREETEXT

O exemplo a seguir pesquisa todos os documentos que contêm palavras relacionadas a vital safety components:

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

Exemplo – CONTAINSTABLE

O exemplo a seguir retorna a ID de descrição e a descrição de todos os produtos para os quais a coluna Descrição contém a palavra “alumínio” ao lado da palavra “leve” ou “peso leve”. Apenas as linhas com classificação 2 ou superior são retornadas.

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

Exemplo – FREETEXTTABLE

O exemplo a seguir estende uma consulta FREETEXTTABLE para retornar primeiro as linhas com classificação mais alta e adicionar a classificação de cada linha à lista de seleção. Para escrever uma consulta semelhante, é necessário saber que ProductDescriptionID é a coluna da chave exclusiva para a tabela ProductDescription.

USE AdventureWorks2022  
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

Esta é uma extensão da mesma consulta que retorna apenas as linhas com classificação 10 ou maior:

USE AdventureWorks2022  
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

Corresponder palavras ou significado

CONTAINS/CONTAINSTABLE e FREETEXT/FREETEXTTABLE são úteis para diferentes tipos de correspondência. As informações a seguir ajudam você a escolher o melhor predicado ou a melhor função para sua consulta:

CONTAINS/CONTAINSTABLE

  • Faça a correspondência de palavras individuais e frases com correspondentes precisos ou difusos (menos precisos).
  • Você também pode fazer o seguinte:
    • Especifique a proximidade de palavras dentro de uma determinada distância uma da outra.
    • Retorne correspondências ponderadas.
    • Combine critérios de pesquisa com operadores lógicos. Para obter mais informações, consulte Usando operadores boolianos (AND, OR e NOT) mais adiante neste artigo.

FREETEXT/FREETEXTTABLE

  • Faça a correspondência de significado, mas não de palavras exatas, palavras especificadas ou frases (a cadeia de caracteres de texto livre).
  • As correspondências serão geradas se qualquer termo ou forma de qualquer termo for encontrada no índice de texto completo de uma coluna especificada.

Comparar predicados e funções

Os predicados CONTAINS/FREETEXT e as funções com valor de conjunto de linhas CONTAINSTABLE/FREETEXTTABLE têm uma sintaxe e opções diferentes. As informações a seguir ajudam você a escolher o melhor predicado ou a melhor função para sua consulta:

Predicados CONTAINS e FREETEXT

Uso. Use os predicados de texto completo CONTAINS e FREETEXT na cláusula WHERE ou HAVING de uma instrução SELECT.

Resultados. Os predicados CONTAINS e FREETEXT retornam um valor TRUE ou FALSE que indica se determinada linha corresponde à consulta de texto completo. As linhas correspondentes são retornadas no conjunto de resultados.

Mais opções. Você pode agrupar os predicados com qualquer um dos outros predicados Transact-SQL, como LIKE e BETWEEN.

Especifique se uma única coluna, uma lista de colunas ou todas as colunas da tabela devem ser pesquisadas.

Opcionalmente, você pode especificar o idioma cujos recursos são usados pela consulta de texto completo para a quebra de palavras e lematização, pesquisas no dicionário de sinônimos e remoção de palavras de ruído.

É possível usar um nome de quatro partes no predicado CONTAINS ou FREETEXT para consultar colunas indexadas de texto completo das tabelas de destino em um servidor vinculado. Para preparar um servidor remoto para receber consultas de texto completo, crie um índice de texto completo nas tabelas e colunas de destino no servidor remoto e, em seguida, adicione o servidor remoto como um servidor vinculado.

Mais informações. Para obter mais informações sobre a sintaxe e os argumentos desses predicados, consulte CONTAINS e FREETEXT.

Funções com valor de conjunto de linhas CONTAINSTABLE e FREETEXTTABLE

Uso. As funções de texto completo CONTAINSTABLE e FREETEXTTABLE são iguais a um nome de tabela comum na cláusula FROM de uma instrução SELECT.

Você precisa especificar a tabela base a ser pesquisada ao usar uma dessas funções. Assim como nos predicados, você pode especificar uma única coluna, uma lista de colunas ou todas as colunas da tabela a ser pesquisada e, se preferir, o idioma cujos recursos são usados por uma dada consulta de texto completo.

Normalmente, você precisa unir os resultados de CONTAINSTABLE ou FREETEXTTABLE à tabela base. Para unir as tabelas, é necessário saber o nome de coluna de chave exclusiva. Esta coluna, que ocorre em todas as tabelas habilitadas para texto completo, é usada para impor linhas exclusivas da tabela (a coluna de chave**exclusiva). Para obter mais informações sobre a coluna de chave, consulte Criar e gerenciar índices de texto completo.

Resultados. Essas funções retornam uma tabela de zero, uma ou mais linhas que correspondem à consulta de texto completo. A tabela retornada contém somente as linhas da tabela base que correspondem aos critérios de seleção especificados no critério de pesquisa de texto completo da função.

As consultas que usam uma dessas funções também retornam um valor de classificação de relevância (RANK) e uma chave de texto completo (KEY) para cada linha retornada, da seguinte maneira:

  • Coluna KEY. A coluna KEY retorna valores exclusivos das linhas retornadas. A coluna KEY pode ser usada para especificar critérios de seleção.
  • Coluna RANK. A coluna RANK retorna um valor de classificação para cada linha que indica o grau de correspondência da linha com os critérios de seleção. Quanto mais alto o valor de classificação do texto ou documento em uma linha, mais relevante será a linha para a consulta de texto completo especificada. Diferentes linhas podem ter a mesma classificação. É possível limitar o número de correspondências a serem retornadas especificando o parâmetro opcional top_n_by_rank . Para obter mais informações, veja Limitar resultados da pesquisa com RANK.

Mais informações. Para obter mais informações sobre a sintaxe e os argumentos dessas funções, consulte CONTAINSTABLE e FREETEXTTABLE.

Tipos específicos de pesquisas

Pesquisar uma palavra ou frase específica (termo simples)

Você pode usar CONTAINS, CONTAINSTABLE, FREETEXTou FREETEXTTABLE para pesquisar uma tabela para uma frase ou palavra específica. Por exemplo, para pesquisar a tabela ProductReview no banco de dados AdventureWorks2022 e localizar todos os comentários sobre um produto com a frase “curva de aprendizado”, use o predicado CONTAINS, da seguinte forma:

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

O critério de pesquisa, nesse caso, “curva de aprendizado”, pode ser complexo, podendo consistir em um ou mais termos.

Mais informações sobre pesquisas de termo simples

Na pesquisa de texto completo, uma palavra (ou token) é uma cadeia de caracteres cujos limites são identificados pelas quebras de palavras apropriadas, seguindo as regras linguísticas do idioma especificado. Uma frase válida consiste em várias palavras, com ou sem sinais de pontuação entre elas.

Por exemplo, "croissant" é uma palavra e "café com leite" é uma frase. Palavras e frases como essas são chamadas de termos simples.

CONTAINS e CONTAINSTABLE procuram uma correspondência exata da frase. FREETEXT e FREETEXTTABLE desmembram a frase em palavras separadas.

Pesquisar uma palavra com um prefixo (termo de prefixo)

Você pode usar CONTAINS ou CONTAINSTABLE para procurar palavras ou frases com um prefixo especificado. Todas as entradas na coluna que contêm o texto que começa com o prefixo especificado são retornadas. Por exemplo, para procurar todas as linhas que contêm o prefixo top-, como em top``ple, top``pinge top. A consulta será semelhante ao exemplo a seguir:

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

Todo texto que corresponder ao texto especificado antes do asterisco (*) será retornado. Se o texto e o asterisco não forem delimitados por aspas duplas, como em CONTAINS (DESCRIPTION, 'top*'), a pesquisa de texto completo não considerará o asterisco como sendo um curinga.

Quando o termo de prefixo for uma frase, todos os tokens que constituírem a frase serão considerados termos de prefixo separados. Todas as linhas que têm palavras que começam com os termos de prefixo serão retornadas. Por exemplo, o termo de prefixo “pão light*” encontrará linhas com o texto de “pãozinho light” ou “pão light”, mas não retornará “pão torrado light”.

Mais informações pesquisas de prefixo

Um termo de prefixo refere-se a uma cadeia de caracteres colocada na frente de uma palavra para gerar uma palavra derivada ou uma forma flexionada.

  • Para um único termo de prefixo, qualquer palavra que comece com o termo especificado fará parte do conjunto de resultados. Por exemplo, o termo "auto*" retorna "automático", "automóvel" e assim por diante.

  • Para uma frase, cada palavra é considerada um termo de prefixo. Por exemplo, o termo “tran auto”” corresponde a “transmissão automática” e “transdutor de automóvel”, mas não corresponde a “transmissão de motor automática”.

Pesquisas de prefixo são compatíveis com CONTAINS e CONTAINSTABLE.

Pesquisar as formas flexionadas de uma palavra específica (termo de geração)

Você pode usar CONTAINS, CONTAINSTABLE, FREETEXTou FREETEXTTABLE para procurar todos os diferentes tempos e conjugações de um verbo ou ambas as formas, singular e plural, de um substantivo (uma pesquisa flexionada) ou sinônimos de uma palavra específica (uma pesquisa de dicionário de sinônimos).

O exemplo a seguir pesquisa qualquer forma de “pé" ("pé", "pés" e assim por diante) na coluna Comments da tabela ProductReview do banco de dados AdventureWorks:

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

A pesquisa de texto completo usa lematizadores, que permitem pesquisar os diferentes tempos e conjugações de um verbo ou as formas singular e plural de um substantivo. Para obter mais informações sobre lematizadores, veja Configurar e gerenciar separadores de palavras e lematizadores para pesquisa.

Mais informações sobre pesquisas de termo de geração

As formas flexionadas são os diferentes tempos e conjugações de um verbo ou as formas singular e plural de um substantivo.

Por exemplo pesquise a forma não flexionada do verbo “dirigir”. Se várias linhas da tabela contivessem as palavras “dirigir”, “dirige”, “dirigiu”, “dirigindo” e “dirigido”, todas elas fariam parte do conjunto de resultados, pois cada uma delas seria uma flexão gerada da palavra dirigir.

Por padrão,FREETEXT e FREETEXTTABLE procuram termos flexionados de todas as palavras especificadas. CONTAINS e CONTAINSTABLE são compatíveis com um argumento INFLECTIONAL opcional.

Pesquisar sinônimos de uma palavra específica

Um dicionário de sinônimos define sinônimos especificados pelo usuário para os termos. Para obter mais informações sobre arquivos de dicionário de sinônimos, consulte Configurar e gerenciar arquivos do dicionário de sinônimos na pesquisa de texto completo.

Por exemplo, se uma entrada, “{carro, automóvel, caminhonete, van}”, for adicionada a um dicionário de sinônimos, você poderá procurar a forma de dicionário de sinônimos da palavra “carro”. Todas as linhas na tabela consultada que contêm as palavras “automóvel”, “caminhonete”, “van” ou “carro” aparecem no conjunto de resultados, pois cada uma dessas palavras pertence ao conjunto de expansão de sinônimo que contém a palavra “carro”.

FREETEXT e FREETEXTTABLE usam o dicionário de sinônimos por padrão. CONTAINS e CONTAINSTABLE são compatíveis com um argumento THESAURUS opcional.

Pesquisar uma palavra NEAR (próxima) a outra palavra

Um termo de proximidade indica palavras ou frases que estão perto umas das outras. Você também pode especificar o número máximo de condições não relacionadas à pesquisa que separam a primeira e a última condição da pesquisa. Além disso, você pode pesquisar palavras ou frases em qualquer ordem, ou na ordem em que as especificar.

Por exemplo, você quer localizar as linhas em que a palavra "gelo" esteja perto de "hóquei" ou em que a frase "patinação no gelo" esteja perto da frase "hóquei no gelo".

CONTAINS e CONTAINSTABLE

Para obter mais informações sobre pesquisas de proximidade, consulte Pesquisar palavras perto de outra palavra com NEAR.

Pesquisar palavras ou frases usando valores ponderados (termo ponderado)

Você pode usar CONTAINSTABLE para procurar palavras ou frases e especificar um valor ponderado. A ponderação, medida como um número de 0,0 a 1,0, indica a importância de cada palavra e frase em um conjunto de palavras e frases. Um peso 0,0 é o mais baixo, e um peso 1,0 é o mais alto.

O exemplo a seguir mostra uma consulta que pesquisa todos os endereços de clientes usando pesos, em que qualquer texto que comece com a cadeia de caracteres “Baía” tem também “Rua” ou “Vista”. Os resultados dão uma classificação mais alta às linhas que contêm mais das palavras especificadas.

USE AdventureWorks2022  
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

Um termo ponderado pode ser usado junto com qualquer termo simples, termo de prefixo, termo de geração ou termo de proximidade.

Mais informações sobre pesquisas de termo ponderado

Em uma pesquisa de termo ponderado, um valor de ponderação indica o grau de importância de cada palavra e frase em um conjunto de palavras e frases. Um valor ponderado de 0,0 é o mais baixo e um valor ponderado de 1,0 é o mais alto.

Por exemplo, em uma consulta que pesquisa vários termos, é possível atribuir a cada palavra da pesquisa um valor equilibrado indicando sua importância com relação às demais palavras da pesquisa. O resultado desse tipo de consulta retorna primeiro as linhas mais relevantes, de acordo com o peso relativo atribuído às palavras da pesquisa. Os conjuntos de resultados contêm documentos ou linhas que, por sua vez, contêm qualquer um dos termos especificados (ou conteúdo entre eles); todavia, alguns resultados serão considerados mais relevantes do que outros devido à variação dos valores ponderados associados aos diferentes termos de pesquisa.

Pesquisas de termo ponderado são compatíveis com CONTAINSTABLE.

Usar AND, OR e NOT (operadores boolianos)

O predicado CONTAINS e a função CONTAINSTABLE usam as mesmas condições de pesquisa. Os dois permitem combinar vários termos de pesquisa usando operadores boolianos – AND, OR e NOT – para executar operações lógicas. Você pode usar AND, por exemplo, para encontrar linhas que contenham “café com leite” e “bagel estilo Nova York”. Você pode usar AND NOT, por exemplo, para encontrar as linhas que contenham “bagel”, mas não contenham “cream cheese”.

Por outro lado, FREETEXT e FREETEXTTABLE tratam os termos boolianos como palavras a serem pesquisadas.

Veja mais informações sobre como agrupar CONTAINS com outros predicados que usam os operadores lógicos AND, OR e NOT em Critério de pesquisa (Transact-SQL).

Exemplo

O exemplo a seguir usa o predicado CONTAINS para pesquisar descrições nas quais a ID de descrição não é igual a 5 e a descrição contém as palavras “Alumínio” e “eixo”. O critério de pesquisa usa o operador booliano AND. Esse exemplo usa a tabela ProductDescription do banco de dados AdventureWorks2022.

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

Maiúsculas e minúsculas, palavras irrelevantes, idioma e dicionário de sinônimos

Ao escrever consultas de texto completo, você também poderá especificar as opções a seguir:

  • Diferenciação de maiúsculas e minúsculas. As consultas de pesquisa de texto completo não diferenciam maiúsculas de minúsculas. No entanto, em japonês há várias ortografias fonéticas em que o conceito de normalização ortográfica é parecido à não diferenciação de maiúsculas e minúsculas (por exemplo, kana = não diferenciação). Esse tipo de normalização ortográfica não tem suporte.

  • Palavras irrelevantes. Ao definir uma consulta de texto completo, o Mecanismo de Texto Completo descarta as palavras irrelevantes (também chamadas de palavras de ruído) dos critérios de pesquisa. Palavras irrelevantes são palavras como "um", "e", "é" ou "o(s)/a(s)", que podem ocorrer com frequência, mas normalmente não ajudam na pesquisa de um determinado texto. As palavras irrelevantes são relacionadas em uma lista de palavras irrelevantes (stoplist). Cada índice de texto completo é associado a uma lista de palavras irrelevantes específica, que determina quais palavras irrelevantes são omitidas da consulta ou do índice no momento da indexação. Para obter mais informações, consulte Configurar e gerenciar palavras irrelevantes e listas de palavras irrelevantes na pesquisa de texto completo.

  • Idioma, com a opção LANGUAGE. Muitos termos de consulta dependem bastante do comportamento do separador de palavras. Para assegurar que você use o separador de palavras (e lematizador) e o arquivo de dicionário de sinônimos corretos, é recomendável especificar a opção LANGUAGE. Para obter mais informações, veja Escolher um idioma ao criar um índice de texto completo.

  • Dicionário de sinônimos. As consultas FREETEXT e FREETEXTTABLE usam o dicionário de sinônimos por padrão. CONTAINS e CONTAINSTABLE dão suporte a um argumento THESAURUS opcional. Para obter mais informações, consulte Configurar e gerenciar arquivos do dicionário de sinônimos na pesquisa de texto completo.

Verificar os resultados da geração de tokens

Depois de aplicar determinada combinação de separador de palavras, dicionário de sinônimos e lista de palavras irrelevantes a uma consulta, você poderá ver como a pesquisa de texto completo gera tokens dos resultados usando a exibição de gerenciamento dinâmico sys.dm_fts_parser. Para obter mais informações, confira sys.dm_fts_parser (Transact-SQL).

Confira também

CONTAINS (Transact-SQL)
CONTAINSTABLE (Transact-SQL)
FREETEXT (Transact-SQL)
FREETEXTTABLE (Transact-SQL)
Criar consultas de pesquisa de texto completo (Visual Database Tools)
Melhorar o desempenho de consultas de texto completo