CONTAINS (Transact-SQL)

É um predicado usado em uma cláusula WHERE para pesquisar colunas que contêm tipos de dados baseados em caracteres para obter correspondências precisas ou difusas (menos precisas) para palavras e frases únicas, a proximidade de palavras com uma determinada distância entre si ou correspondências ponderadas.

No SQL Server, é possível usar nomes de quatro partes em predicados de texto completo CONTAINS ou FREETEXT para executar consultas em servidores vinculados.

CONTAINS pode pesquisar:

• Uma palavra ou frase.

• O prefixo de uma palavra ou frase.

• Uma palavra próxima a outra.

• Uma palavra gerada a partir de outra por flexão (por exemplo, a palavra guia é uma raiz flexional de guiar, guiou, guiando e guiado).

• Uma palavra que é sinônima de outra usando um dicionário de sinônimos (por exemplo, a palavra metal pode ter sinônimos, como alumínio e aço).

Convenções de sintaxe Transact-SQL

Sintaxe

CONTAINS
      ( { column_name | ( column_list ) | * } 
          , '<contains_search_condition>'     
   [ , LANGUAGE language_term ]
      ) 

<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 ] 
        ) 

<AND> ::= 
     { AND | & }

<AND NOT> ::= 
     { AND NOT | &! }

<OR> ::= 
     { OR | | }

Argumentos

• column_name
  É o nome de uma coluna indexada de texto completo da tabela especificada na cláusula FROM. As colunas podem ser do tipo char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary ou varbinary(max).

• column_list
  Especifica duas ou mais colunas, separadas por vírgulas. A column_list deve estar entre parênteses. A menos que language_term esteja especificado, o idioma de todas as colunas de column_list deve ser o mesmo.

• *
  Especifica que a consulta pesquisará todas as colunas indexadas de texto completo na tabela especificada na cláusula FROM para o critério de pesquisa determinado. As colunas na cláusula CONTAINS devem se originar de uma única tabela que tenha um índice de texto completo. A menos que language_term esteja especificado, o idioma de todas as colunas da tabela deve ser o mesmo.

• LANGUAGE language_term
  É o idioma a ser usado para quebra de palavras, lematizações, expansões e substituições de dicionários de sinônimos e palavra de ruído (ou palavra irrelevante) como parte da consulta. Esse parâmetro é opcional.

  Se os documentos de idiomas diferentes forem armazenados juntos como BLOBs (objetos binários grandes) em uma única coluna, o LCID (identificador de localidade) de um determinado documento determinará qual idioma a ser usado para indexar seu conteúdo. Ao consultar esse tipo de coluna, se você especificar LANGUAGE language_term poderá aumentar a probabilidade de uma boa correspondência.

  language_term pode ser especificado como uma cadeia de caracteres, um valor inteiro ou hexadecimal que corresponda ao LCID (identificador local) 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.

  Quando especificado como uma cadeia de caracteres, language_term corresponde ao valor da coluna alias na exibição de compatibilidade de sys.syslanguages (Transact-SQL). 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 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.

• <contains_search_condition>
  Especifica o texto a ser pesquisado em column_name e as condições para uma correspondência.

  <contains_search_condition> é nvarchar. Uma conversão implícita acontece quando outro tipo de dados de caractere é usado como entrada. No exemplo a seguir, a variável @SearchWord, que está definida como varchar(30), causa uma conversão implícita no predicado CONTAINS.

  USE AdventureWorks2008R2;
  GO
  DECLARE @SearchWord varchar(30)
  SET @SearchWord ='performance'
  SELECT Description 
  FROM Production.ProductDescription 
  WHERE CONTAINS(Description, @SearchWord);
  

  Como a "detecção de parâmetro" não funciona em conversão, use nvarchar para obter um melhor desempenho. No exemplo, declare @SearchWord como nvarchar(30).

  USE AdventureWorks2008R2;
  GO
  DECLARE @SearchWord nvarchar(30)
  SET @SearchWord = N'performance'
  SELECT Description 
  FROM Production.ProductDescription 
  WHERE CONTAINS(Description, @SearchWord);
  

  Você também pode usar a dica de consulta OPTIMIZE FOR para casos em que é gerado um plano não ideal.

• word
  É uma cadeia de caracteres sem espaços ou pontuação.

• phrase
  É uma ou mais palavras com espaços entre si.

  Observação
  Alguns idiomas, como aqueles escritos em algumas regiões da Ásia, podem ter frases que consistem em uma ou mais palavras sem espaços entre si.

• <simple_term>
  Especifica uma correspondência para uma palavra ou frase exata. Exemplos de termos simples válidos são "blue berry", "blueberry" e "Microsoft SQL Server". As frases devem estar entre aspas duplas (""). As palavras em uma frase devem aparecer na mesma ordem conforme especificado em <contains_search_condition> pois elas aparecem na coluna do banco de dados. A pesquisa de caracteres na palavra ou frase não diferencia maiúsculas e minúsculas. As palavras de ruído (ou palavras irrelevantes) (como um, e ou o) em colunas indexadas de texto completo não são armazenadas no índice de texto completo. Se uma palavra de ruído for usada em uma pesquisa de uma única palavra, o SQL Server retornará uma mensagem de erro indicando que a consulta contém apenas palavras de ruído. O SQL Server inclui uma lista padrão de palavras de ruído no diretório \Mssql\Binn\FTERef de cada instância do SQL Server.

  A pontuação é ignorada. Portanto, CONTAINS(testing, "computer failure") corresponde a uma linha com o valor: "Onde está meu computador? A falha ao localizá-lo pode ser dispendiosa." Para obter mais informações sobre o comportamento do separador de palavras, consulte Separadores de palavras e lematizadores.

• <prefix_term>
  Especifica uma correspondência de palavras ou frases que começam com o texto especificado. Coloque o termo de prefixo entre aspas duplas ("") e adicione um asterisco (*) antes de fechar aspas, para que todo o texto que comece com o termo simples especificado antes do asterisco seja encontrado. A cláusula deve ser especificada deste modo: CONTAINS (column, '"text*"'). O asterisco corresponde a zero, um ou mais caracteres (de palavra(s)-raiz na palavra ou frase). Se o texto e o asterisco não estiverem delimitados por aspas duplas e o predicado for CONTAINS (column, 'text*'), a pesquisa de texto completo considerará o asterisco como um caractere e pesquisará uma correspondência exata para text*. O mecanismo de texto completo não localizará palavras com o caractere de asterisco (*) porque os separadores de palavras costumam ignorar tais caracteres.

  Quando <prefix_term> for uma frase, cada palavra contida na frase será considerada um prefixo separado. Portanto, uma consulta especificando um termo de prefixo "local wine*" corresponde a qualquer linha com o texto "locally wined and dined" e assim por diante.

• <generation_term>
  Especifica uma correspondência de palavras quando os termos simples incluídos têm variáveis da palavra original a ser pesquisada.

• INFLECTIONAL
  Especifica que o lematizador dependente de idioma será usado no termo simples especificado. O comportamento do lematizador é definido com base em regras lexicais de cada idioma específico. O idioma neutro não tem um lematizador associado. O idioma das colunas que estão sendo consultadas é usado para fazer referência ao lematizador desejado. Se language_term for especificado, será usado o lematizador correspondente para aquele idioma.

  Um determinado <simple_term> dentro de um <generation_term> não corresponderá a substantivos e verbos.

• THESAURUS
  Especifica que será usado o dicionário de sinônimos correspondente ao idioma de texto completo da coluna ou ao idioma especificado na consulta. A correspondência do padrão ou dos padrões mais longos de <simple_term> é feita pela enciclopédia, e os termos adicionais são gerados para expandir ou substituir o padrão original. Se não for encontrada uma correspondência para o <simple_term> inteiro ou parte dele, a parte não correspondente será tratada como um simple_term. Para obter mais informações sobre o dicionário de sinônimos de pesquisa de texto completo, consulte Configuração do Thesaurus.

• <proximity_term>
  Especifica uma correspondência de palavras ou frases que devem estar no documento que está sendo pesquisado. Como o operador AND, <proximity_term> exige que os dois termos de pesquisa existam no documento que está sendo pesquisado.

  • NEAR | ~
    Indica que a palavra ou frase em cada lado do operador NEAR ou ~ deve ocorrer em um documento para uma correspondência a ser retornada. Várias condições de proximidade podem ser encadeadas, como em a NEAR b NEAR c ou a ~ b ~ c. Os termos de proximidade encadeados devem estar todos no documento para que uma correspondência seja retornada.

    Quando usados na função CONTAINSTABLE, a proximidade dos termos de pesquisa afeta a classificação de cada documento. Quanto maior a proximidade entre os termos de pesquisa coincidentes em um documento, maior será a classificação do documento. Se os termos de pesquisa coincidentes forem >50 termos separadamente, a classificação retornada no documento será 0.

    Por exemplo, CONTAINS (column_name, 'raposa NEAR galinha') e CONTAINSTABLE (table_name, column_name, 'raposa ~ galinha') retornam todos os documentos na coluna especificada que contêm "raposa" e "galinha". Além disso, CONTAINSTABLE retorna uma classificação para cada documento baseado na proximidade de "raposa" e "galinha". Por exemplo, se um documento contiver a sentença "A raposa comeu a galinha", sua classificação será alta.

    NEAR indica a distância lógica entre termos, e não a distância absoluta entre eles. Por exemplo, termos dentro de frases diferentes ou orações dentro de um parágrafo são tratadas de forma muito diferente dos termos na mesma frase ou oração, independentemente da proximidade real, supondo que eles estejam menos relacionados. Da mesma forma, termos em parágrafos diferentes são tratados de forma muito mais diferente.

• <weighted_term>
  Especifica que as linhas correspondentes (retornadas pela consulta) correspondem a uma lista de palavras e frases, sendo que cada uma, opcionalmente, recebe um valor de importância.

• ISABOUT
  Especifica a palavra-chave <weighted_term>.

  • WEIGHT(weight_value)
    Especifica um valor do peso que é um número de 0,0 a 1,0. Cada componente em <weighted_term> pode incluir um weight_value. weight_value é uma maneira de alterar a forma como as várias partes de uma consulta afetam o valor de classificação atribuído a cada linha que corresponde à consulta. WEIGHT não afeta os resultados das consultas CONTAINS, mas impacta a classificação nas consultas CONTAINSTABLE.

    Observação
    O separador decimal é sempre um ponto, independentemente da localidade do sistema operacional.

• { AND | & } | { AND NOT | &! } | { OR | | }
  Especifica uma operação lógica entre dois critérios de pesquisa CONTAINS.

  • AND | &
    Indica que os dois critérios de pesquisa CONTAINS devem ser atendidos para haver uma correspondência. O símbolo E comercial (&) pode ser usado em vez da palavra-chave AND para representar o operador AND.

  • AND NOT | &!
    Indica que o segundo critério de pesquisa não deve estar presente para haver uma correspondência. O símbolo E comercial seguido pelo símbolo de ponto de exclamação (&!) pode ser usado em vez da palavra-chave AND NOT para representar o operador AND NOT.

  • OR | |
    Indica que qualquer um dos dois critérios de pesquisa CONTAINS deve ser atendido para haver uma correspondência. O símbolo de barra vertical (|) pode ser usado em vez da palavra-chave OR para representar o operador OR.

    Quando <contains_search_condition> contém grupos entre parênteses, esses grupos são avaliados primeiro. Depois de avaliar grupos entre parênteses, essas regras se aplicam ao usar estes operadores lógicos com os critérios de pesquisa CONTAINS:

    • NOT é aplicado antes de AND.

    • NOT só pode ocorrer depois de AND, como em AND NOT. O operador OR NOT não é permitido. NOT não pode ser especificado antes do primeiro termo. Por exemplo, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ) não é válido.

    • AND é aplicado antes de OR.

    • Operadores booleanos do mesmo tipo (AND, OR) são associativos e, portanto, podem ser aplicados em qualquer ordem.

    • n
      É um espaço reservado que indica que várias condições e termos de pesquisa CONTAINS podem ser especificados dentro dele.

Comentários

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

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

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.

Comparação de LIKE com a pesquisa de texto completo

Em comparação com a pesquisa de texto completo, o predicado Transact-SQLLIKE funciona apenas com padrões de caractere. Além disso, não é possível usar o predicado LIKE para consultar dados binários formatados. Além disso, uma consulta LIKE feita em uma grande quantidade de dados de texto não estruturados é bem mais lenta do que uma consulta de texto completo equivalente feita nos mesmos dados. Uma consulta LIKE executada em milhões de linhas de dados pode levar muitos minutos, enquanto uma consulta de texto completo pode demorar alguns segundos ou menos para ser executada nos mesmos dados, dependendo do número de linhas retornadas.

Exemplos

A. 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 AdventureWorks2008R2;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
   AND CONTAINS(Name, 'Mountain');
GO

B. Usando CONTAINS e frase em <simple_term>

O exemplo a seguir retorna todos os produtos contendo a expressão "Mountain" ou "Road".

USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Mountain" OR "Road" ')
GO

C. Usando CONTAINS com <prefix_term>

O exemplo a seguir retorna todos os nomes de produtos com pelo menos uma palavra que comece com a cadeia de prefixos na coluna Name.

USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
GO

D. Usando CONTAINS e OR com <prefix_term>

O exemplo a seguir retorna todas as descrições de categoria contendo cadeias de caracteres com prefixos de "chain" ou "full".

USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO

E. Usando CONTAINS com <proximity_term>

O exemplo a seguir retorna todos os nomes de produtos com a palavra bike próxima à palavra performance.

USE AdventureWorks2008R2;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');
GO

F. Usando CONTAINS com <generation_term>

O exemplo a seguir pesquisa todos os produtos com as palavras da forma ride: riding, ridden e assim por diante.

USE AdventureWorks2008R2;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');
GO

G. Usando CONTAINS com <weighted_term>

O exemplo a seguir pesquisa todos os nomes de produtos contendo as palavras performance, comfortable ou smooth, e diferentes pesos são dados a cada uma.

USE AdventureWorks2008R2;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'ISABOUT (performance weight (.8), 
comfortable weight (.4), smooth weight (.2) )' );
GO

H. Usando CONTAINS com variáveis

O exemplo a seguir usa uma variável em vez de um termo de pesquisa específico.

USE AdventureWorks2008R2;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'Performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE CONTAINS(Description, @SearchWord);
GO

I. Usando CONTAINS com um operador lógico (AND)

O exemplo a seguir usa a tabela ProductDescription do banco de dados AdventureWorks2008R2. 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". O critério de pesquisa usa o operador booleano AND.

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

J. Usando CONTAINS para verificar uma inserção de linha

O exemplo a seguir usa CONTAINS dentro de uma subconsulta SELECT. Usando o banco de dados AdventureWorks2008R2, a consulta obtém o valor de todos os comentários na tabela ProductReview de um ciclo específico. O critério de pesquisa usa o operador booleano AND.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.ProductReview 
(ProductID, ReviewerName, EmailAddress, Rating, Comments) 
VALUES
(780, 'John Smith', 'john@fourthcoffee.com', 5, 
'The Mountain-200 Silver from Adventure Works Cycles meets and exceeds expectations. I enjoyed the smooth ride down the roads of Redmond')
 
-- Given the full-text catalog for these tables is Adv_ft_ctlg, 
-- with change_tracking on so that the full-text indexes are updated automatically.
WAITFOR DELAY '00:00:30'   
-- Wait 30 seconds to make sure that the full-text index gets updated.
 
SELECT r.Comments, p.Name
FROM Production.ProductReview r
JOIN Production.Product p 
ON
 r.ProductID = p.ProductID
 
AND r.ProductID = (SELECT ProductID
                  FROM Production.ProductReview
                  WHERE CONTAINS (Comments, 
                                 ' Adventure Works AND 
                                   Redmond AND 
                                   "Mountain-200 Silver" '))

GO

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)

CONTAINSTABLE (Transact-SQL)

FREETEXT (Transact-SQL)

FREETEXTTABLE (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

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

Pesquisa de texto completo (SQL Server)