Query with Full-Text Search

Para definir pesquisas de texto completo, SQL Server consultas de texto completo usam os predicados de texto completo (CONTAINS e FREETEXT) e as funções (CONTAINSTABLE e FREETEXTTABLE). Eles dão suporte à sintaxe Transact-SQL avançada que dá suporte a uma variedade de formas de termos de consulta. Para gravar consultas de texto completo, você deve saber quando e como usar esses predicados e funções.

Visão geral dos predicados de texto completo (CONTAINS e FREETEXT)

Os predicados CONTAINS e FREETEXT retornam um valor TRUE ou FALSE. Eles podem ser usados somente para especificar critérios de seleção para determinar se uma dada linha corresponde à consulta de texto completo. As linhas correspondentes são retornadas no conjunto de resultados. CONTAINS e FREETEXT são especificados na cláusula WHERE ou HAVING de uma instrução SELECT. Eles podem ser combinados com qualquer um dos outros predicados Transact-SQL, como LIKE e BETWEEN.

Ao usar CONTAINS ou FREETEXT, você pode especificar se uma única coluna, uma lista de colunas ou todas as colunas da tabela devem ser pesquisadas. Se preferir, você pode especificar o idioma cujos recursos serão usados por uma dada consulta de texto completo para quebra de palavras e lematização, pesquisas no dicionário de sinônimos e remoção de palavras de ruído.

CONTAINS e FREETEXT são úteis para diferentes tipos de correspondências, como segue:

• Use CONTAINS (ou CONTAINSTABLE) para obter correspondências precisas ou difusas (menos precisas) para palavras e frases únicas, a proximidade entre as palavras dentro de uma determinada distância ou correspondências ponderadas. Ao usar CONTAINS, você deve especificar pelo menos um critério de pesquisa que especifique o texto procurado e as condições que determinam correspondências.

  Você pode usar uma operação lógica entre condições de pesquisa. Para obter mais informações, consulte Usando operadores boolianos - AND, OR, AND NOT (em CONTAINS e CONTAINSTABLE), mais adiante neste tópico.

• Use FREETEXT (ou FREETEXTTABLE) para fazer a correspondência de significado, mas não da frase exata, de palavras, frases ou sentenças especificadas (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.

É 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.

Exemplos

a. Usando CONTAINS com <simple_term>

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

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

B. Usando FREETEXT para pesquisar palavras que contêm valores de caracteres especificados

As pesquisas de exemplo a seguir para todos os documentos que contêm as palavras relacionadas a componentes vitais, de segurança.

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

Visão geral das funções de texto completo (CONTAINSTABLE e FREETEXTTABLE)

As funções CONTAINSTABLE e FREETEXTTABLE são referenciadas como um nome de tabela comum na cláusula FROM de uma instrução SELECT. Elas 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 atendem 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 retornam um valor de classificação de relevância (RANK) e uma chave de texto completo (KEY) para cada linha, 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. Observe que 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.

Ao usar uma dessas funções, você deve especificar a tabela base que deve ser objeto da pesquisa de texto completo. 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 serão usados por uma dada consulta de texto completo.

CONTAINSTABLE é útil para os mesmos tipos de correspondências que CONTAINS, e FREETEXTTABLE é útil para os mesmos tipos de correspondências que FREETEXT. Para obter mais informações, veja Visão geral dos predicados de texto completo (CONTAINS e FREETEXT), acima neste tópico. Ao executar consultas que usam as funções CONTAINSTABLE e FREETEXTTABLE, você deve unir explicitamente linhas retornadas com as linhas na tabela base SQL Server.

Normalmente, o resultado de CONTAINSTABLE ou FREETEXTTABLE precisa ser associado à tabela base. Nesses casos, você precisa saber o nome da 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, veja Gerenciar índices de texto completo.

Exemplos

a. Usando 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" perto da palavra "light" ou da palavra "lightweight". Somente linhas com um valor de classificação igual a 2 ou superior são retornadas.

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. Usando 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 especificar a consulta, você deve saber que ProductDescriptionID é a coluna de chave exclusiva da ProductDescription tabela.

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  

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

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  

Usando operadores boolianos - AND, OR e NOT - em CONTAINS e CONTAINSTABLE

O predicado CONTAINS e a função CONTAINSTABLE usam as mesmas condições de pesquisa. Ambos dão suporte à combinação de vários termos de pesquisa usando os operadores boolianos AND, OR e AND NOT-para executar operações lógicas. Você pode usar AND, por exemplo, para localizar linhas que contêm "expresso" e "rosca". Você pode usar AND NOT, por exemplo, para localizar as linhas que contêm "rosca" mas que não contêm "cream cheese".

Para obter informações sobre como combinar CONTAINS com outros predicados que usam os operadores lógicos AND, OR e NOT, consulte Condição de pesquisa (Transact-SQL).

Exemplo

O exemplo a seguir usa a tabela ProductDescription do banco de dados AdventureWorks2012. A consulta 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 "Aluminum" e "spindle". A condição de pesquisa usa o operador booleano AND.

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

Considerações adicionais para consultas de texto completo

Ao escrever consultas de texto completo, considere também o seguinte:

• 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.

• 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, veja Configurar e gerenciar palavras irrelevantes e listas de palavras irrelevantes para pesquisa de texto completo.

• O dicionário de sinônimos

  As consultas FREETEXT e FREETEXTTABLE usam o dicionário de sinônimos por padrão. CONTAINS e CONTAINSTABLE suportam um argumento THESAURUS opcional.

• Diferenciar maiúsculas de 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.

Consultando colunas varbinary(max) e xml

Se uma coluna varbinary(max), varbinary ou xml tiver um índice de texto completo, poderá ser consultada usando os predicados (CONTAINS e FREETEXT) e as funções (CONTAINSTABLE e FREETEXTTABLE) de texto completo, como qualquer outra coluna indexada de texto completo.

Dados varbinary (max) ou varbinary

Uma única coluna varbinary(max) ou varbinary pode armazenar muitos tipos de documentos. O SQL Server oferece suporte a qualquer tipo de documento para o qual um filtro está instalado e disponível no sistema operacional. O tipo de cada documento é identificado pela extensão de arquivo do documento. Por exemplo, no caso de uma extensão de arquivo .doc, a pesquisa de texto completo usa o filtro que dá suporte a documentos do Microsoft Word. Para obter uma lista dos tipos de documento disponíveis, veja a exibição de catálogo sys.fulltext_document_types .

Observe que o Mecanismo de Texto Completo pode aproveitar os filtros existentes instalados no sistema operacional. Para que você possa usar filtros do sistema operacional, separadores de palavras e lematizadores, carregue-os na instância do servidor da seguinte maneira:

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

Para criar um índice de texto completo em uma coluna varbinary(max), o Mecanismo de Texto Completo precisa de acesso às extensões de arquivo dos documentos na coluna varbinary(max). Esta informação deve ser armazenada em uma coluna de tabela, chamada de coluna de tipo, que deve estar associada à coluna varbinary(max) no índice de texto completo. Ao indexar um documento, o Mecanismo de Texto Completo utiliza a extensão de arquivo na coluna de tipo para identificar o filtro a ser usado.

Dados xml

Uma coluna de tipo de dados xml armazena apenas fragmentos e documentos XML, e somente o filtro XML é usado para os documentos. Portanto, uma coluna de tipo é desnecessária. Em colunas xml, o índice de texto completo indexa o conteúdo dos elementos XML, mas ignora a marcação XML. Os valores de atributos são indexados como texto completo, a menos que sejam valores numéricos. Marcas de elemento são usadas como limites do token. Há suporte a fragmentos e documentos XML ou HTML bem formados que contêm vários idiomas.

Para obter mais informações sobre como consultar em uma xml coluna, consulte Usar Full-Text Pesquisa com Colunas XML.

Formatos suportados de termos de consulta

Esta seção resume o suporte fornecido em cada formato de consulta por predicados de texto completo e funções com valores de conjunto de linhas.

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

Você pode usar CONTAINS, CONTAINSTABLE, FREETEXTou FREETEXTTABLE para pesquisar uma tabela para uma frase específica. Por exemplo, se você quiser pesquisar a ProductReview tabela no banco de dados AdventureWorks2012 para localizar todos os comentários sobre um produto com a frase "curva de aprendizado", poderá usar o predicado CONTAINS da seguinte maneira:

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

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

Fazendo pesquisas de 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 tem esta aparência:

USE AdventureWorks2012  
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”.

Pesquisando 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 “foot" ("foot", "feet" e assim por diante) na coluna Comments da tabela ProductReview do banco de dados AdventureWorks .

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

Pesquisando 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 procura todos os endereços de clientes usando pesos, em que qualquer texto que comece com a cadeia de caracteres "Bay" tem também "Street" ou "View". Os resultados dão uma classificação mais alta a essas linhas que contêm mais das palavras especificadas.

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  

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

Exibindo o resultado da geração de tokens de uma combinação de separador de palavras, dicionário de sinônimos e lista de palavras irrelevantes

Depois de aplicar determinada combinação de separador de palavras, dicionário de sinônimos e lista de palavras irrelevantes a uma entrada de cadeia de caracteres de consulta, você poderá exibir o resultado da geração de tokens usando a exibição de gerenciamento dinâmico sys.dm_fts_parser. Para obter mais informações, consulte sys.dm_fts_parser (Transact-SQL).

Consulte 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