SQL Server - utilização de collate para junções de tabelas e filtros de consulta

Pessoal,

Hoje eu fui questionado sobre um recurso do SQL Server interessante que eu acho que vale a pena compartilhar aqui. Trata-se da utilização da instrução collate para fins de junção de tabelas e filtro de consulta com dados alfanuméricos que contemplam acentuação e case (caixa-alta e caixa-baixa) variável.

Por exemplo, eu gostaria que o usuário do meu sistema conseguisse realizar a consulta de todos as pessoas com o nome "João" no sistema. Porém como é impraticável validar se "João" se escreve com "ã" minha base de dados está sujeita a ter as seguintes variações:

1. João[...algum sobrenome 01], e
2. Joao[...algum sobrenome 02]

Portanto, se o usuário do meu sistema realizar a consulta utilizando como palavra-chave "João" somente o registro do "João[...algum sobrenome 01]" será retornado. Este problema se agrava se tivermos uma base de dados case-sensitive, nesta situação minha base de dados está sujeita a ter uma variação exponencialmente maior do que a demonstrada acima, como por exemplo:

1. joao,
2. Joao,
3. JOao,
4. JOAo,
5. JOAO,
6. etc... 

Existem muitas outras variações do mesmo nome, somente considerando o case, agora imagine a quantidade de variações unindo o case e a acentuação, multiplique por 2, some 6, divida por 2 e subtraia pelo número que você imaginou... Deu 3... Que bruxaria é essa?! Kkkkkkkk

Voltando ao assunto, a pergunta que não quer calar é, como eu vou fazer para conseguir realizar a consulta com todas estas variações de forma que isso fique transparente para o usuário do meu sistema?

A resposta é simples, basta utilizar a instrução collate, não vou entrar no mérito de explicar a fundo pra que serve esta instrução, a princípio pense no collate como o conjunto de caracteres que poderão ser utilizados em sua base de dados e saiba que a parametrização dela no ato da extração de dados poderá ajudá-lo a resolver o cenário citado acima.

Quando a base de dados é criada, utilizando a instrução CREATE DATABASE Exemplo o SQL Server considera o collate do servidor em que ele se encontra hospedado (imagem 1), no meu caso o Latin1_General_CI_AS.

 

Imagem 01 - Collate do servidor.

Procedimento
Para acessar a janela Server Properties faça:

1. Object Explorer (atalho F8)
2. Clique botão direito no nodo raiz, e
3. Clique na opção Properties

O grande segredo está no sulfixo do collate destacado acima em vermelho(CI) e laranja(AS) que significam case-insensitive e accent-sensitive, respectivamente. Mas o que isso quer dizer?

• Case-insensitive: palavras com o mesmo conjunto de caracteres são considerados iguais, independentemente de conter letras maiúsculas ou minúsculas.
  Exemplo: joao = Joao = JOAO...
• Accent-sensitive: palavras com o mesmo conjunto de caracteres "decoradas" com algum tipo de acentução são consideradas diferentes.
  Exemplo: João ≠ Joao

Com tudo isso em mente, vamos logo a solução, até porque eu já cansei de escrever e quero jogar video-game. Para realizar a consulta englobando todas as possíveis variações de um determinado nome, basta mudarmos o AS para AI que significa accent-insensitive, ou seja, todas as palavras são consideradas iguais independentemente de estarem "decoradas" com acentuação ou não. E caso a sua base de dados for case-sensitive provavelmente o collate terá os caracteres CS em sua composição, lembre-se de alterá-lo para CI.

Segue abaixo um exemplo prático e o respectivo resultado (imagem 02).
SELECT    Nome
FROM       Pessoa
WHERE    Nome COLLATE Latin1_General_CI_AI   LIKE   '%Joao%'

 

Imagem 02 - Resultado de consulta.

Como junções de tabelas também se dão por operações relacionais, nada impede de fazermos junções com nomes ou palavras com case-insensitive e accent-insensitive. Experimente!

No link abaixo você consegue realizar o download do arquivo de exemplo completo, inclusive com junção de tabelas.

https://docs.google.com/open?id=0B5VB4jqgZuesVFlBbmU5eHlkX3M

Espero ter ajudado.

Fonte: http://msdn.microsoft.com/en-us/library/ms180175(v=sql.105).aspx