Consulta com pesquisa de texto completo

Para definir consultas de texto completo, o SQL Server usa os predicados de texto completo (CONTAINS e FREETEXT) e as funções (CONTAINSTABLE e FREETEXTTABLE). Elas dão suporte à avançada sintaxe Transact-SQL que comporta 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.

Neste tópico

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

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

• Usando operadores boolianos – AND, OR, AND NOT – em CONTAINS e CONTAINSTABLE

• Considerações adicionais para consultas de texto completo

• Consultando colunas varbinary(max) e xml

• Formatos suportados de termos de consulta

• 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

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 dos outros predicados Transact-SQL, como LIKE e BETWEEN.

Observação
Para obter informações sobre a sintaxe e os argumentos desses predicados, consulte CONTAINS (Transact-SQL) e FREETEXT (Transact-SQL).

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.

Observação
Predicados de texto completo não são permitidos na Cláusula OUTPUT quando o nível de compatibilidade do banco de dados está definido como 100.

[TOP]

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

[TOP]

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.

Observação
Para obter mais informações sobre a sintaxe e os argumentos dessas funções, consulte CONTAINSTABLE (Transact-SQL) e FREETEXTTABLE (Transact-SQL).

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, consulte Limite resultados de 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, consulte Visão geral dos predicados de texto completo (CONTAINS e FREETEXT), anteriormente neste tópico. Ao executar consultas que usam as funções CONTAINSTABLE e FREETEXTTABLE, você deve associar explicitamente as linhas retornadas às linhas na tabela base do 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, consulte Gerenciar índices de texto completo.

[TOP]

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 Description contém a palavra "aluminum" ao lado da palavra "light" ou "lightweight". Apenas as linhas com valor de classificação 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 da chave exclusiva para a tabela ProductDescription.

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

[TOP]

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. Os dois permitem combinar vários termos de pesquisa usando operadores boolianos — AND, OR, 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".

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

Para obter mais informações sobre como combinar CONTAINS com outros predicados que usam os operadores lógicos AND, OR e NOT, consulte Critério 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 procurar 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". O critério de pesquisa usa o operador booliano AND.

USE AdventureWorks2012
GO

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

[TOP]

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, consulte 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, consulte Configurar e gerenciar palavras irrelevantes e listas de palavras irrelevantes (stoplists) 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.

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

[TOP]

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.

Importante
A pesquisa de texto completo também funciona com colunas de imagem. No entanto, o tipo de dados image será removido em uma versão futura do SQL Server. Evite usar esse tipo de dados em um novo trabalho de desenvolvimento e planeje modificar os aplicativos que o utilizam. Use o tipo de dados varbinary(max) em vez disso.

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, consulte a exibição do 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.

[TOP]

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 fazer consultas em uma coluna xml, consulte Usar a pesquisa de texto completo com colunas XML.

[TOP]

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.

Observação
Para obter a sintaxe um determinado termo da consulta, clique nos links correspondentes na coluna Supported by da tabela seguinte.

[TOP]

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

Você pode usar CONTAINS, CONTAINSTABLE, FREETEXT ou FREETEXTTABLE para pesquisar uma tabela para uma frase específica. Por exemplo, para pesquisar a tabela ProductReview no banco de dados AdventureWorks2012 e localizar todos os comentários sobre um produto com a frase "curva de aprendizado", use o predicado CONTAINS, como segue:

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.

[TOP]

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 topple, topping e top. A consulta é semelhante a esta:

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

[TOP]

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

Você pode usar CONTAINS, CONTAINSTABLE, FREETEXT ou 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

Observação
A pesquisa de texto completo usa lematizadores, que permitem que você pesquise os diversos tempos e conjugações de um verbo, ou as formas singular e plural de um substantivo. Para obter mais informações sobre lematizadores, consulte Configurar e gerenciar separadores de palavras e lematizadores de pesquisa.

[TOP]

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.

[TOP]

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 uma 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ê pode 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).

[TOP]

Consulte também

Tarefas

Criar consultas de pesquisa de texto completo (Visual Database Tools)

Referência

CONTAINS (Transact-SQL)

CONTAINSTABLE (Transact-SQL)

FREETEXT (Transact-SQL)

FREETEXTTABLE (Transact-SQL)

Conceitos

Melhorar o desempenho de consultas de texto completo