Freigeben über

Consultas Full-Text em Português no SQL Server 2005

  • Artikel

Hoje vou escrever sobre um recurso muito popular no SQL Server 2005, que tem ganhado maior receptividade a cada dia: o Full-Text Search. Trata-se de uma tecnologia importantíssima que compõe o SQL Server. Vou falar brevemente aqui sobre seu propósito, como configurar Full-Text Search em português (PT-BR) e alguns cenários de exemplo. Espero que você goste.

Definição

O Full-Text Search (ou simplesmente FTS) permite que sejam feitas pesquisas de palavras em campos do tipo texto, XML ou binário, com flexibilizações semânticas. Imagine ter campos de texto livre em seu banco de dados (comentários, revisões), documentos do Word, PowerPoint ou outros formatos armazenados em colunas do tipo binary (e varbinary) e ter a possibilidade de efetuar uma pesquisa indexada a estes campos, de forma muito simples, obtendo como resultado não apenas os registros que contenham a palavra pesquisada, mas também registros que contenham diferentes formas semânticas da mesma palavra, ou até mesmo outras palavras que estejam relacionadas aos termos pesquisados. Esta é a funcionalidade que o Full-Text Search entrega.

Um exemplo: em uma aplicação web de uma livraria, que contém informações sobre livros, você armazena em uma das colunas as revisões feitas por usuários sobre um livro e, em outra coluna, um documento Microsoft Office Word com o primeiro capítulo do mesmo livro. O Full-Text Search permite que você pesquise as duas colunas pela palavra “Projeto” e que sejam retornados os resultados “Projeto”, “Projetos”, “Projetei”, “Projetaremos”, entre outras formas, com uma coluna que indica um ranking da relevância daquele resultado para a pesquisa solicitada. Ele permite ainda que se relacione a palavra “Obra” a “Projeto”, de modo que ao se pesquisar a palavra “Projeto” resultados que contenham o termo “Obra” também sejam retornados. Isto é especialmente relevante quando lidamos com marcas de produtos. Pode-se, por exemplo, fazer que seja retornado um registro com a palavra “SQL Server” ao se pesquisar pelo termo “SGBD”.

O interessante é que esta busca de semântica pode ser feita em diversos idiomas, incluindo o nosso português do Brasil (PT-BR). O sistema de quebra de palavras e de flexibilização leva em consideração as regras do nosso idioma para fazer a consulta. O Full-Text Search é um recurso integrado ao SQL Server. Ele é, na verdade, o mecanismo utilizado por ferramentas como o Microsoft SharePoint Server.

Faz sentido?

Cenários de exemplo

Além do exemplo acima, da livraria, os cenários abaixo ajudam a ilustrar a utilização do Full-Text Search:

  1. Gerenciamento Eletrônico de Documentos (GED)
    • Pesquisa de currículos em uma base de dados de RH (ex.: pesquisar as palavras “SQL Server”, “Educação”)
    • Pesquisa de material técnico
  2. E-Business
    • Navegação em Internet Banking (ex.: ao pesquisar o termo “Investimento” o resultado pode trazer “Investimentos”, “CDB”, “DI”, “Fundo de Ações”, etc)
    • Encontrar itens em catálogos de ofertas
  3. Departamento Legal
    • Identificar conteúdo em e-mail
    • Pesquisa de jurisprudência

Thesaurus, Noise Words e Idiomas

A obtenção de resultados a partir da associação de palavras relacionadas é produto da funcionalidade de Thesaurus do Full-Text Search. Você pode relacionar palavras em um arquivo XML de modo que resultados que sejam relevantes ao termo pesquisado sejam retornados ao usuário. O exemplo acima com Internet Banking ilustra bem este cenário (retornar “Ações” ao se pesquisar “Investimento”).

Pode-se também configurar palavras a serem ignoradas pelo mecanismo de pesquisa. Este recurso é chamado de Noise Words e se baseia na configuração de um arquivo de parâmetro contendo quaisquer palavras que precisem ser ignoradas, melhorando a relevância da pesquisa. Alguns exemplos são “Para”, “Onde” e “Se”.

Quando são construídos os catálogos e os índices Full-Text especifica-se o idioma daquele índice. A partir daí o SQL Server utiliza o “quebrador” (Word Breaker) que seja do idioma correto. Ainda assim, pode-se forçar a utilização de um Word breaker diferente durante a consulta para, por exemplo, fazer consultas utilizando de forma excepcional outro idioma. Em outras palavras: a pesquisa não é limitada ao idioma especificado ao criar o índice. Atualmente, o Full-Text Search está disponível em 23 idiomas diferentes.

Configuração do Full-Text Search em Português do Brasil (PT-BR)

É necessária configuração manual no SQL Server 2005 para configuração do Full-Text Search no nosso idioma. Isto é necessário, pois o Word Breaker para português foi desenvolvido por uma empresa externa.

AVISO: execute os passos abaixo sob sua responsabilidade. Como qualquer alteração no Windows Registry, é recomendável que o procedimento seja testado em ambiente de laboratório e que se tenha um backup completo do ambiente.

  1. Clique em Start-> Run. Digite regedit.exe e clique em Ok;
  2. Navegue até a chave; HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\CLSID;
  3. Clique em Edit-> New e selecione Key. Digite {25B7FD48-5404-4BEB-9D80-B6982AF404FD} e pressione Enter para confirmar;
  4. Clique com o botão direito sobre o valor “(Default)” no painel da direita e selecione Modify. Insira o valor “ptblr.dll” e confirme;
  5. Clique em Edit->New e selecione Key novamente. Digite {D5FCDD7E-DBFF-473F-BCCD-3AFD1890EA85} e pressione Enter para confirmar;
  6. Clique com o botão direito sobre o valor “(Default)” no painel da direita e selecione Modify. Insira o valor “ptblr.dll” e confirme;
  7. Navegue até a chave HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\Language\ptb
  8. Clique em Edit->New e selecione String Value
  9. Digite NoiseFile e pressione Enter
  10. Dê um clique duplo em NoiseFile e digite o caminho “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseptb.txt”
  11. Repita os passos 8 até 10 para criar valores com os seguintes dados:
String Value TsaurusFile C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\tsptb.xml
DWORD Value Locale 00000416
String Value WBreakerClass {25B7FD48-5404-4BEB-9D80-B6982AF404FD}
String Value StemmerClass {D5FCDD7E-DBFF-473F-BCCD-3AFD1890EA85}

OBSERVAÇÃO: Os caminhos sugeridos os passos 2,7 e 10 podem variar se você estiver utilizando uma instância nomeada. Os passos acima também estão descritos no artigo https://support.microsoft.com/kb/908441/en-us

Utilizando Full-Text Search

Aí vai um exemplo muito rápido de utilização do Full-Text Search com busca por semântica:

SELECT Descricao FROM Comentarios

WHERE CONTAINS (Descricao, 'FORMSOF(INFLECTIONAL, "colocamos")')

Resultados (visualizados de forma parcial devido ao tamanho do campo utilizado):

image

Como disse, este é um exemplo bem simples que construí para esta demonstração. Para exemplos mais elaborados por favor deixe um comentário, ou simplesmente visite “Querying SQL Server Using Full-Text Search” https://msdn.microsoft.com/en-us/library/ms142559(SQL.100).aspx. Este artigo inclui os seguintes tópicos:

  • Full-Text Search Query Fundamentals
  • Searching for Specific Word or Phrase (Simple Term)
  • Performing Prefix Searches
  • Searching for the Inflectional Form of a Specific Word (Generation Term)
  • Searching for Words or Phrases Using Weighted Values (Weighted Term)
  • Searching for Words or Phrases Close to Another Word or Phrase (Proximity Term)
  • Querying varbinary(max) and xml Columns
  • Querying Multiple Columns
  • Querying Linked Servers
  • Integrating Full-Text Search and Transact-SQL Predicates
  • Comparing Full-Text Functions and Full-Text Predicates

Como sempre, sinta-se a vontade para deixar suas dúvidas e comentários.

** Obrigado ao Fernando Barros pelas notas de correção com o valor correto para a StemmerClass e com o nome da dll do word breaker.

Comments

  • Anonymous
    June 05, 2008
    Parabéns, didático e esclarecedor o artigo. Para quem está pensando em usar o FT, mostra as facilidades e opções de uso.

  • Anonymous
    October 08, 2008
    The comment has been removed

  • Anonymous
    November 07, 2008
    Olá, Se você tentar executar a operação acima você terá um erro desconhecido. As correções necessárias pra funcionar são as seguintes:

  1. O código para StemmerClass é {D5FCDD7E-DBFF-473F-BCCD-3AFD1890EA85} e nao {2....
  2. O nome correto da dll é "ptblr.dll". (a letra l vem antes da letra r). De qualquer forma, o conteúdo está ótimo. []'s, Fernando Barros

  • Anonymous
    November 25, 2008
    Fernando, Obrigado pelo aviso. Já atualizei o post com as informações corretas. Abraços, Pericles Rocha

  • Anonymous
    February 14, 2009
    esta show, Muito bom Obrigado

  • Anonymous
    June 04, 2009
    A idéia do artigo foi bem apresentada, bem didática. Porém não sei onde errei seguindo os passos, que dá um erro! Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Produto' because it is not full-text indexed. utilizando esse script: SELECT DescricaoCompleta FROM Produto WHERE CONTAINS (DescricaoCompleta, 'FORMSOF(INFLECTIONAL, "dia")') quais configurações a mais tenho que fazer na instalação do sql server, ou mesmo recurso a ser ativado para que isso funcione?! Estou utilizando o SQL Server 2005 e 2008. Esse erro acima foi no 2008! Obrigado pela atenção!

  • Anonymous
    August 20, 2009
    Por acaso não é possível fornecer as propriedades a adicionar no registry para português europeu? Obrigado

  • Anonymous
    December 01, 2010
    Parabéns, muito bom o artigo. Estou iniciado estudos para certificar SQL Server e seu artigo foi útil para mim.