CONTAINSTABLE (Transact-SQL)

Retorna uma tabela com zero, uma ou mais linhas para as colunas que contêm tipos de dados com base em caracteres para obter correspondências precisas ou difusas (menos precisas) com palavras individuais e frases, a proximidade entre as palavras em uma determinada distância umas das outras, ou correspondências ponderadas. CONTAINSTABLE só pode ser referenciada na cláusula FROM de uma instrução SELECT, como se fosse um nome de tabela comum.

Consultas que usam CONTAINSTABLE especificam consultas de texto completo do tipo contém que retornam um valor de classificação de relevância (RANK) e chave de texto completo (KEY) para cada linha. A função CONTAINSTABLE e o predicado CONTAINS usam os mesmos critérios de pesquisa.

Convenções de sintaxe Transact-SQL

Sintaxe

CONTAINSTABLE ( table , { column_name | (column_list ) | * } , ' < contains_search_condition > ' 
     [ , LANGUAGE language_term] 
  [ , top_n_by_rank ] 
          ) 
< contains_search_condition > ::= 
    { < simple_term > 
    | < prefix_term > 
    | < generation_term > 
    | < proximity_term > 
    |  < weighted_term > 
    } 
    | { ( < contains_search_condition > ) 
    { { AND | & } | { AND NOT | &! } | { OR | | } } 
     < contains_search_condition > [ ...n ] 
    }
< simple_term > ::= 
          word | " phrase "
< prefix term > ::= 
     { "word * " | "phrase *" } 
< generation_term > ::= 
     FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] ) 
< proximity_term > ::= 
     { < simple_term > | < prefix_term > } 
     { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ] 
< weighted_term > ::= 
     ISABOUT
        ( { { 
  < simple_term > 
  | < prefix_term > 
  | < generation_term > 
  | < proximity_term > 
  } 
   [ WEIGHT ( weight_value ) ] 
   } [ ,...n ] 
        )

Argumentos

• table
  É o nome de uma tabela que foi indexada por texto completo. table pode ser um nome de objeto de banco de dados de uma, duas, três ou quatro partes. Durante a consulta a uma exibição, só uma tabela base indexada com texto completo pode ser envolvida.

  A table não pode especificar um nome de servidor e não pode ser usada em consultas em servidores vinculados.

• column_name
  É o nome de uma ou mais colunas indexadas para pesquisa de texto completo. As colunas podem ser do tipo char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary ou varbinary(max).

• column_list
  Indica que várias colunas, separadas por uma vírgula, podem ser especificadas. column_list deve estar entre parênteses. A menos que language_term esteja especificado, o idioma de todas as colunas de column_list deverá ser o mesmo.

• *
  Especifica que todas as colunas indexadas com texto completo na table devem ser usadas para procurar a condição de pesquisa fornecida. A menos que language_term esteja especificado, o idioma de todas as colunas da tabela deverá ser o mesmo.

• LANGUAGE language_term
  É o idioma cujos recursos serão usados para separação de palavras, lematização, dicionário de sinônimos e remoção de palavras de ruído (ou stopword) como parte da consulta. Este parâmetro é opcional e pode ser especificado como uma cadeia de caracteres, um número inteiro ou um valor hexadecimal que corresponda ao LCID (ID de localidade) de um idioma. Se language_term for especificado, o idioma que ele representa será aplicado a todos os elementos do critério de pesquisa. Se nenhum valor for especificado, o idioma de texto completo da coluna será usado.

  Se documentos de idiomas diferentes forem armazenados em conjunto como BLOBs (objetos binários grandes) em uma única coluna, o LCID de um determinado documento determinará qual idioma será usado para indexar seu conteúdo. Ao consultar essa coluna, a especificação de LANGUAGElanguage_term pode aumentar a probabilidade de uma boa correspondência.

  Quando especificado como uma cadeia de caracteres, language_term corresponde ao valor da coluna alias na exibição de compatibilidade sys.syslanguages. A cadeia de caracteres deve estar entre aspas simples, como em 'language_term'. Quando especificado como um inteiro, language_term é o LCID real que identifica o idioma. Quando especificado como um valor hexadecimal, language_term é 0x seguido pelo valor hexadecimal do LCID. O valor hexadecimal não deve exceder oito dígitos, incluindo zeros à esquerda.

  Se o valor estiver no formato DBCS (conjunto de caracteres de dois bytes), o Microsoft SQL Server o converterá em Unicode.

  Se o idioma especificado não for válido ou se não houver nenhum recurso instalado que corresponda ao idioma, o SQL Server retornará um erro. Para usar os recursos de idioma neutros, especifique 0x0 como language_term.

• top_n_by_rank
  Especifica que somente as n correspondências mais bem classificadas, em ordem descendente, sejam retornadas. Aplica-se apenas quando um valor inteiro, n, for especificado. Se top_n_by_rank estiver combinado com outros parâmetros, a consulta poderá retornar menos linhas que o número de linhas que realmente correspondem a todos os predicados. top_n_by_rank permite aumentar o desempenho da consulta chamando novamente apenas os acertos mais relevantes.

• <contains_search_condition>
  Especifica o texto a ser pesquisado em column_name e os critérios de correspondência. Para obter mais informações sobre os critérios de pesquisa, consulte CONTAINS (Transact-SQL).

Comentários

Predicados e funções de texto completo funcionam em uma única tabela que está implícita no predicado FROM. Para pesquisar em várias tabelas, use uma tabela unida na cláusula FROM para pesquisar em um conjunto de resultados que é o produto de duas ou mais tabelas.

A tabela retornada possui uma coluna denominada KEY que contém valores de chave de texto completo. Cada tabela indexada de texto completo possui uma coluna cujos valores têm garantia de exclusividade, e os valores retornados na coluna KEY são valores de chave de texto completo das linhas que correspondem aos critérios de seleção especificados no critério de pesquisa contains. A propriedade TableFulltextKeyColumn, obtida a partir da função OBJECTPROPERTYEX, fornece a identidade dessa coluna de chave exclusiva. Para obter a ID da coluna associada à chave de texto completo do índice de texto completo, use sys.fulltext_indexes. Para obter mais informações, consulte sys.fulltext_indexes (Transact-SQL).

Para obter as linhas desejadas da tabela original, especifique uma junção com as linhas CONTAINSTABLE. O formulário típico da cláusula FROM de uma instrução SELECT que usa CONTAINSTABLE é:

SELECT select_list
FROM table AS FT_TBL INNER JOIN
   CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
   ON FT_TBL.unique_key_column = KEY_TBL.[KEY]

A tabela produzida por CONTAINSTABLE contém uma coluna chamada RANK. A coluna RANK é um valor (de 0 a 1000) para cada linha que indica a qualidade da correspondência da linha com os critérios de seleção. Geralmente, esse valor de classificação é usado de uma destas maneiras na instrução SELECT:

• Na cláusula ORDER BY para retornar as linhas mais bem classificadas como as primeiras linhas da tabela.

• Na lista de seleção para ver o valor de classificação atribuído a cada linha.

CONTAINSTABLE não será reconhecido como uma palavra-chave se o nível de compatibilidade for inferior a 70. Para obter mais informações, consulte sp_dbcmptlevel (Transact-SQL).

Permissões

As permissões de execução estão disponíveis somente para usuários com os privilégios SELECT adequados na tabela ou nas colunas referenciadas da tabela.

Exemplos

A. Retornando valores de classificação usando CONTAINSTABLE

O exemplo a seguir pesquisa todos os nomes de produtos que contêm as palavras breads, fish ou beers e pesos diferentes são conferidos a cada uma. Para cada linha retornada que corresponda a esses critérios de pesquisa, será mostrada a proximidade relativa (valor de classificação) da correspondência. Além disso, as linhas mais bem classificadas serão retornadas primeiro.

USE Northwind;
GO
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
    FROM Categories AS FT_TBL 
        INNER JOIN CONTAINSTABLE(Categories, Description, 
        'ISABOUT (breads weight (.8), 
        fish weight (.4), beers weight (.2) )' ) AS KEY_TBL
            ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
GO

B. Retornando valores de classificação maiores que o valor especificado usando CONTAINSTABLE

O exemplo a seguir retorna a descrição e o nome de todas as categorias de alimentos para as quais a coluna Description contém as palavras "sweet and savory" próximas da palavra sauces ou candies. Todas as linhas com o nome de categoria Seafood serão desconsideradas. Apenas as linhas com valor de classificação 2 ou maior serão retornadas.

USE Northwind;
GO
SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK
FROM Categories AS FT_TBL 
    INNER JOIN CONTAINSTABLE (Categories, Description, 
        '("sweet and savory" NEAR sauces) OR
        ("sweet and savory" NEAR candies)'
        ) AS KEY_TBL
        ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
    AND FT_TBL.CategoryName <> 'Seafood'
ORDER BY KEY_TBL.RANK DESC;
GO

C. Retornando os 10 melhores resultados usando CONTAINSTABLE e top_n_by_rank

O exemplo a seguir retorna a descrição e o nome das 10 principais categorias de alimentos cuja coluna Description contenha as palavras "sweet and savory" próximo à palavra "sauces" ou "candies".

USE Northwind;
SELECT FT_TBL.Description, FT_TBL.CategoryName , KEY_TBL.RANK
FROM Categories AS FT_TBL 
    INNER JOIN CONTAINSTABLE (Categories, Description, 
        '("sweet and savory" NEAR sauces) OR
        ("sweet and savory" NEAR candies)', 10)
        AS KEY_TBL
        ON FT_TBL.CategoryID = KEY_TBL.[KEY]

GO

D. Especificando o argumento LANGUAGE

O exemplo a seguir mostra o uso do argumento LANGUAGE.

USE Northwind;
SELECT FT_TBL.Description , FT_TBL.CategoryName , KEY_TBL.RANK
FROM dbo.Categories AS FT_TBL 
    INNER JOIN CONTAINSTABLE (dbo.Categories, Description, 
        '("sweet and savory" NEAR sauces) OR
        ("sweet and savory" NEAR candies)',LANGUAGE N'English', 10) 
        AS KEY_TBL
        ON FT_TBL.CategoryID = KEY_TBL.[KEY];

Observação
O argumento LANGUAGE language_term não é necessário para se usar top_n_by_rank.

Consulte também

Tarefas

Como criar um catálogo de texto completo (SQL Server Management Studio)

Como iniciar o Assistente para Indexação de Texto Completo (SQL Server Management Studio)

Como criar consultas de pesquisa de texto completo (Visual Database Tools)

Referência

CREATE FULLTEXT CATALOG (Transact-SQL)

CREATE FULLTEXT INDEX (Transact-SQL)

CONTAINS (Transact-SQL)

Funções de conjunto de linhas (Transact-SQL)

SELECT (Transact-SQL)

WHERE (Transact-SQL)

Conceitos

Introdução à pesquisa de texto completo

Visão geral da indexação de texto completo

Visão geral de predicados e funções de texto completo

Conceitos básicos de junção

Consultando o SQL Server com pesquisa de texto completo