Consulta com Full-Text

Para definir pesquisas de texto completo, as consultas de texto completo do SQL Server usam os predicados de texto completo (CONTAINS e FREETEXT) e as funções (CONTAINSTABLE e FREETEXTTABLE. Elas suportam a sintaxe rica Transact-SQL que oferece uma variedade de formas de termos de consulta. Para escrever consultas de texto completo, você deve aprender quando e como usar esses predicados e funções.

Visão geral dos predicados de Full-Text (CONTAINS e FREETEXT)

Os predicados CONTAINS e FREETEXT retornam um valor TRUE ou FALSE. Eles só podem ser usados para especificar critérios de seleção para determinar se uma determinada 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 uma única coluna, uma lista de colunas ou todas as colunas na tabela a serem pesquisadas. Opcionalmente, você pode especificar o idioma cujos recursos serão usados em uma consulta de texto completo para quebra e derivação de palavras, pesquisas de dicionário de sinônimos e remoção de palavras irrelevantes.

CONTAINS e FREETEXT são úteis para diferentes tipos de correspondências, da seguinte maneira:

• Use CONTAINS (ou CONTAINSTABLE) para correspondências exatas ou aproximadas (menos exatas) com palavras e frases únicas, a proximidade de palavras dentro de uma certa distância entre si ou correspondências ponderadas. Ao usar CONTAINS, você deve especificar pelo menos uma condição de pesquisa que especifique o texto que você está procurando e as condições que determinam correspondências.

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

• Use FREETEXT (ou FREETEXTTABLE) para corresponder ao significado, mas não à redação exata, de palavras, frases ou frases especificadas (a cadeia de caracteres de texto livre). As correspondências serão geradas se qualquer termo ou forma de qualquer termo for encontrado no índice de texto completo de uma coluna especificada.

Você pode 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

Um. Usando CONTAINS com <simple_term>

O exemplo a seguir localiza todos os produtos com um preço de $80.99 que contêm 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 Full-Text (CONTAINSTABLE e FREETEXTTABLE)

As funções CONTAINSTABLE e FREETEXTTABLE são referenciadas como um nome de tabela regular na cláusula FROM de uma instrução SELECT. Eles retornam uma tabela contendo zero, uma ou mais linhas que correspondam à consulta de texto completo. A tabela retornada contém apenas linhas da tabela base que correspondem aos critérios de seleção especificados na condição 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 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 quão bem a linha correspondeu aos critérios de seleção. Quanto maior o valor de classificação do texto ou documento em uma linha, mais relevante será a linha para a consulta de texto completo fornecida. Observe que linhas diferentes podem ser classificadas de forma idêntica. Você pode limitar o número de correspondências a serem retornadas especificando o parâmetro top_n_by_rank opcional. Para obter mais informações, consulte Limitar resultados da pesquisa com RANK.

Ao usar qualquer uma dessas funções, você deve especificar a tabela base que deve ser pesquisada em texto completo. Assim como acontece com os predicados, você pode especificar uma única coluna, uma lista de colunas ou todas as colunas na tabela a serem pesquisadas e, opcionalmente, o idioma cujos recursos serão usados por determinada 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 Full-Text (CONTAINS e FREETEXT) anteriormente 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 do SQL Server.

Normalmente, o resultado de CONTAINSTABLE ou FREETEXTTABLE precisa ser unido à tabela base. Nesses casos, você precisa saber o nome exclusivo da coluna de chave. Esta coluna, que ocorre em todas as tabelas habilitadas para texto completo, é usada para impor linhas exclusivas para a tabela (a coluna de chave exclusiva**). Para obter mais informações, consulte Gerenciar índices de Full-Text.

Exemplos

Um. 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 mais bem classificadas e adicionar a classificação de cada linha à lista de resultados. Para especificar a consulta, você deve saber que ProductDescriptionID é a coluna de chave exclusiva para a 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  

Aqui está uma extensão da mesma consulta que retorna apenas linhas com um valor de classificação igual a 10 ou superior:

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 operadores boolianos E, OU e E NÃO para executar operações lógicas. Você pode usar AND, por exemplo, para encontrar linhas que contêm "latte" e "bagel no estilo Nova York". Você pode usar AND NOT, por exemplo, para localizar as linhas que contêm "bagel", mas 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 a palavra "Alumínio" e a palavra "eixo". A condição de pesquisa usa o operador AND Boolean.

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

Considerações adicionais para consultas Full-Text

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

• A opção LANGUAGE

  Muitos termos de consulta dependem muito do comportamento do separador de palavras. Para garantir que você esteja usando o separador de palavras correto (e o lematizador) e o arquivo de sinônimos, recomendamos que você especifique a opção LANGUAGE. Para obter mais informações, consulte Escolher um idioma ao criar um índice de Full-Text.

• Palavras irrelevantes

  Ao definir uma consulta de texto completo, o mecanismo Full-Text descarta palavras irrelevantes (também chamadas de palavras vazias) dos critérios de pesquisa. Stopwords são palavras como "a", "e", "é" ou "o", que podem ocorrer com frequência, mas que normalmente não ajudam ao procurar por um texto específico. Stopwords são listadas em uma lista de stopwords. Cada índice de texto completo está 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 dão suporte a um argumento opcional THESAURUS.

• Diferenciação de maiúsculas e minúsculas

  Consultas de pesquisa de texto completo não fazem distinção entre maiúsculas e minúsculas. No entanto, em japonês, há várias ortografias fonéticas nas quais o conceito de normalização ortográfica é semelhante à insensibilidade à diferença de maiúsculas e minúsculas (por exemplo, kana significa insensibilidade). Não há suporte para esse tipo de normalização ortográfica.

Consultando colunas varbinary(max) e xml

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

dados varbinary(max) ou varbinary

Uma única varbinary(max) ou varbinary coluna pode armazenar muitos tipos de documentos. O SQL Server dá suporte a qualquer tipo de documento para o qual um filtro está instalado e disponível no sistema operacional. O tipo de documento de cada documento é identificado pela extensão de arquivo do documento. Por exemplo, para 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 de tipos de documento disponíveis, consulte a exibição de catálogo sys.fulltext_document_types.

Observe que o mecanismo de Full-Text pode aproveitar os filtros existentes instalados no sistema operacional. Antes de poder usar filtros do sistema operacional, separadores de palavras e lematizadores, você deve carregá-los 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 varbinary(max) coluna, o mecanismo de Full-Text precisa de acesso às extensões de arquivo dos documentos na varbinary(max) coluna. Essas informações devem ser armazenadas em uma coluna de tabela, chamada coluna de tipo, que deve ser associada à varbinary(max) coluna no índice de texto completo. Ao indexar um documento, o mecanismo de Full-Text usa a extensão de arquivo na coluna de tipo para identificar qual filtro usar.

dados xml

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

Para obter mais informações sobre como consultar em uma xml coluna, consulte Use Full-Text Search with XML Columns.

Formulários de termos de consulta com suporte

Esta seção resume o suporte fornecido para cada forma de consulta pelos predicados de texto completo e funções com valor de conjunto de linhas.

Pesquisando palavras ou frases específicas (termo simples)

Você pode usar CONTAINS, CONTAINSTABLE, FREETEXT ou FREETEXTTABLE para pesquisar uma tabela em busca de uma frase específica. Por exemplo, se você quiser pesquisar a ProductReview tabela no banco de dados AdventureWorks2012 para encontrar 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  

A condição de pesquisa, nesse caso, "curva de aprendizado", pode ser bastante complexa e pode ser composta por um ou mais termos

Executando buscas por prefixo (termo prefixo)

Você pode usar CONTAINS ou CONTAINSTABLE para pesquisar palavras ou frases com um prefixo especificado. Todas as entradas na coluna que contêm texto começando com o prefixo especificado são retornadas. Por exemplo, para pesquisar 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 o texto que corresponde 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 um curinga..

Quando o termo de prefixo é uma frase, cada token que compõe a frase é considerado um termo de prefixo separado. Todas as linhas que têm palavras começando com os termos de prefixo serão retornadas. Por exemplo, o termo de prefixo "pão leve*" encontrará linhas com texto de "pão leve", "levemente empanado" ou "pão leve", mas não retornará "pão levemente torrado".

Pesquisando formas flexionais de uma palavra específica (termo gerador)

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

O exemplo Comments a seguir procura qualquer forma de "pé" ("pé", "pés" e assim por diante) na coluna da ProductReview tabela no AdventureWorks banco de dados.

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 pesquisar palavras ou frases e especificar um valor de ponderação. O peso, medido como um número de 0,0 a 1,0, indica a importância de cada palavra e frase dentro de um conjunto de palavras e frases. Um peso de 0,0 é o mais baixo, e um peso de 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 sequência "Bay" tenha "Street" ou "View". Os resultados dão uma classificação mais alta para as linhas que contêm mais 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 em conjunto com qualquer termo simples, termo de prefixo, termo de geração ou termo de proximidade.

Exibindo o resultado da tokenização de uma combinação de divisor de palavras, dicionário de sinônimos e lista de palavras de parada.

Depois de aplicar um determinado separador de palavras, dicionário de sinônimos e lista de palavras a serem ignoradas a uma cadeia de caracteres de entrada de consulta, você pode exibir o resultado da tokenização usando a visão de gerenciamento dinâmico sys.dm_fts_parser. Para obter mais informações, consulte sys.dm_fts_parser (Transact-SQL).

Consulte Também

CONTÉM (Transact-SQL)
CONTAINSTABLE (Transact-SQL)
FREETEXT (Transact-SQL)
FREETEXTTABLE (Transact-SQL)
Criar Consultas de Pesquisa Full-Text (Visual Database Tools)
Melhorar o desempenho de consultas Full-Text