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:
- João[...algum sobrenome 01], e
- 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:
- joao,
- Joao,
- JOao,
- JOAo,
- JOAO,
- 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:
- Object Explorer (atalho F8)
- Clique botão direito no nodo raiz, e
- 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
