fonética
(francês phonétique)s. f.
1. [Linguística] Parte da linguística que trata dos sons articulados, considerados do ponto de vista físico, acústico e articulatório, como elementos dos vocábulos.
Essa é a definição do dicionário, mas basicamente a fonética é um "ramo" que estuda a pronuncia de uma palavra, visto que há diferenças entre a escrita e a pronuncia. Por exemplo, quantas maneiras podemos escrever o nome Wilham?
Wilian, Willian, Uyliam… Ainda mais no Brasil, e por falar nisso aqui há uma "limitação", a fonética é fortemente vinculada a língua. No brasil o som da letra "i" é um, nos EUA é outro… Ai!
Voltando aos nomes, no exemplo citado o som pode ser "fielmente" definido como "uiliam", então, na teoria, teríamos que bolar algum algoritmo para gerar o fonema da palavra, e nesse caso, independente da maneira como o nome for escrito, o resultado fonético seria o mesmo.
Complicado não!? Mas a boa notícia é que existem inúmeros estudos para gerar essa solução. Um deles é o Soundex. Criado em 1918, por Robert C. Russell e Margaret K. Odell. Esse algoritmo gera uma chave baseada na primeira letra e três números, "calculados" pelas letras na sequencia. Nesse algoritmo, o nosso Willian, viraria W450, e suas variações também.
O SQL Server já implementa nativamente o Soundex. Veja os exemplos a seguir:
Mas como "alegria de pobre dura pouco"… Há alguns poréns, primeiramente o Soundex é um algoritmo que funciona muito bem com a língua inglesa. Como disse anteriormente a fonética é extremamente ligada a sua língua. Palavras compostas e alguns casos da língua portuguesa não se dão muito bem com essa implementação do Soundex. Veja:
Vamos superar esse #mimimi!? Primeiramente vamos implementar o Soundex em C# e depois implementar adaptações necessárias para a nossa língua.
A base para isso foi tirada da revista Mundo .NET número 26, Casos de sucesso – Pesquisa Fonética em português; Tribunal de Justiça de Mato Grosso do Sul. Nesse caso o tribunal tinha problemas em localizar pessoas no sistema pelo nome. Antes da solução era preciso de informar o nome exato para o sistema buscar as informações.
Vamos definir alguns métodos para a implementação básica do Soundex:
// -> Obtem o soudex de uma string
private static string GetSoundex(string value)
{
value = value.ToUpper(); // -> Normaliza letras em Maiuscula
var result = new StringBuilder();
foreach (char item in value)
if (char.IsLetter(item)) // -> Limpa String
AddCharacter(result, item); // -> Adiciona um char Soudex
// -> Limpa as exceções geradas
result.Replace(".", string.Empty);
// -> Arruma o tamanho no padrão soudex
// 4 caracteres.
FixLength(result);
return result.ToString();
}
// -> Preenche com 0 nos casos menor q quatro,
// ou pega os 4 primeiros caracteres.
private static void FixLength(StringBuilder result)
{
var length = result.Length;
if (length < 4)
result.Append(new string('0', 4 - length));
else
result.Length = 4;
}
// -> Adiciona o caractere soudex, cuidando para não repetir.
private static void AddCharacter(StringBuilder result, char item)
{
if (result.Length == 0)
{
result.Append(item);
}
else
{
var code = GetSoundexDigit(item);
if (code != result[result.Length - 1].ToString())
result.Append(code);
}
}
// -> Algoritmo de substituição padrão Soudex.
private static string GetSoundexDigit(char item)
{
var charString = item.ToString();
if ("BFPV".Contains(charString))
return "1";
else if ("CGJKQSXZ".Contains(charString))
return "2";
else if ("DT".Contains(charString))
return "3";
else if ("L".Contains(charString))
return "4";
else if ("MN".Contains(charString))
return "5";
else if ("R".Contains(charString))
return "6";
else
return ".";
}
Como a língua portuguesa dispões de muita sujeira e quinquilharia acentos e símbolos, temos de tratá-las com as funções a seguir:
// -> Recebe várias letras, separadas por virgula, para serem substituídas por uma letra específica.
private static string ReplaceMultiplo(string value, string from, string to)
{
var fromPattern = string.Format("({0})", string.Join(")|(", from.Split(','))).Replace(".", @"\.");
return Regex.Replace(value, fromPattern, to);
}
private static string GetSoundexBR(string value)
{
value = value.ToUpper();
value = ReplaceMultiplo(value, "Á,À,Ã,Â,Ä", "A");
value = ReplaceMultiplo(value, "É,È,Ê,Ë", "E");
value = ReplaceMultiplo(value, "Í,Ì,Î,Ï", "I");
value = ReplaceMultiplo(value, "Ó,Ò,Ö,Õ,Ô", "O");
value = ReplaceMultiplo(value, "Ú,Ù,Û,Ü", "U");
value = ReplaceMultiplo(value, ".,-,", string.Empty);
value = ReplaceMultiplo(value, "0,1,2,3,4,5,6,7,8,9,0", string.Empty);
value = ReplaceMultiplo(value, "Y", "I");
value = ReplaceMultiplo(value, "PH", "F");
value = ReplaceMultiplo(value, "GE", "JE");
value = ReplaceMultiplo(value, "GI", "JI");
value = ReplaceMultiplo(value, "CA", "KA");
value = ReplaceMultiplo(value, "CE", "SE");
value = ReplaceMultiplo(value, "CI", "SI");
value = ReplaceMultiplo(value, "CO", "KO");
value = ReplaceMultiplo(value, "CU", "KU");
value = ReplaceMultiplo(value, "Ç", "S");
value = ReplaceMultiplo(value, "WAS", "WS");
value = ReplaceMultiplo(value, "WA", "VA");
value = ReplaceMultiplo(value, "WO", "VO");
value = ReplaceMultiplo(value, "WU", "VU");
value = ReplaceMultiplo(value, "WI", "UI");
// -> Depois de tratar a lingua portuguesa
// Geramos o soudex padrão.
value = GetSoundex(value);
return value;
}
Agora vem uma parte legal, vamos implementar isso diretamente no SQL Server, usando a CLR do SQL Server, ou seja, a vantagem do SQL Server utilizar o .NET Framework.
Para tal, vamos habilitar a execução da CLR executando os comando a seguir no SQL Server:
sp_configure 'clr', 1
go
RECONFIGURE
go
Em seguida crie no Visual Studio um projeto para o SQL Server:
Após isso o Visual Studio pede para se conectar a base onde a CLR será implantada:
O projeto já vem estruturado para o SQL Server e com exemplos de teste:
Nesse projeto existem várias opções prontas para implementar muitas das funcionalidades do banco de dados. Aqui usaremos uma Function:
Cada método que deverá ser função tem que ser decorado com o atributo "[Microsoft.SqlServer.Server.SqlFunction]". Outro ponto importante é que é possível trabalhar com os tipos do SQL Server. Eles são fundamentais para receber entradas e devolver as saídas.
Resta fazer os métodos que serão reconhecidas pelo SQL Server, ou seja, decoradas com o atributo citado acima. A classe final fica assim:
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using System.Text;
using System.Linq;
public partial class UserDefinedFunctions
{
/*
para ligar o clr no sql server:
sp_configure 'clr', 1
go
RECONFIGURE
go
*/
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SoundexBR(SqlString value)
{
StringBuilder palavras = new StringBuilder();
value = NormalizarEspacos(value);
var valorNormalizado = value.ToString();
// -> Gera o Soundex de cada palavra da string
valorNormalizado.Split(' ').ToList().ForEach(palavra => palavras.Append(string.Format("{0} ", GetSoundexBR(palavra))));
// -> Retorna o tipo string do SQL Server
return new SqlString(palavras.ToString().Trim());
}
// -> Retira espaços excedentes.
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString NormalizarEspacos(SqlString value)
{
var result = value.ToString().Trim();
while (result.Contains(" "))
result = result.Replace(" ", " ");
return new SqlString(result);
}
// -> Recebe várias letras, separadas por virgula, para serem substituídas por uma letra específica.
private static string ReplaceMultiplo(string value, string from, string to)
{
var fromPattern = string.Format("({0})", string.Join(")|(", from.Split(','))).Replace(".", @"\.");
return Regex.Replace(value, fromPattern, to);
}
private static string GetSoundexBR(string value)
{
value = value.ToUpper();
value = ReplaceMultiplo(value, "Á,À,Ã,Â,Ä", "A");
value = ReplaceMultiplo(value, "É,È,Ê,Ë", "E");
value = ReplaceMultiplo(value, "Í,Ì,Î,Ï", "I");
value = ReplaceMultiplo(value, "Ó,Ò,Ö,Õ,Ô", "O");
value = ReplaceMultiplo(value, "Ú,Ù,Û,Ü", "U");
value = ReplaceMultiplo(value, ".,-,", string.Empty);
value = ReplaceMultiplo(value, "0,1,2,3,4,5,6,7,8,9,0", string.Empty);
value = ReplaceMultiplo(value, "Y", "I");
value = ReplaceMultiplo(value, "PH", "F");
value = ReplaceMultiplo(value, "GE", "JE");
value = ReplaceMultiplo(value, "GI", "JI");
value = ReplaceMultiplo(value, "CA", "KA");
value = ReplaceMultiplo(value, "CE", "SE");
value = ReplaceMultiplo(value, "CI", "SI");
value = ReplaceMultiplo(value, "CO", "KO");
value = ReplaceMultiplo(value, "CU", "KU");
value = ReplaceMultiplo(value, "Ç", "S");
value = ReplaceMultiplo(value, "WAS", "WS");
value = ReplaceMultiplo(value, "WA", "VA");
value = ReplaceMultiplo(value, "WO", "VO");
value = ReplaceMultiplo(value, "WU", "VU");
value = ReplaceMultiplo(value, "WI", "UI");
// -> Depois de tratar a lingua portuguesa
// Geramos o soudex padrão.
value = GetSoundex(value);
return value;
}
// -> Obtem o soudex de uma string
private static string GetSoundex(string value)
{
value = value.ToUpper(); // -> Normaliza letras em Maiuscula
var result = new StringBuilder();
foreach (char item in value)
if (char.IsLetter(item)) // -> Limpa String
AddCharacter(result, item); // -> Adiciona um char Soudex
// -> Limpa as exceções geradas
result.Replace(".", string.Empty);
// -> Arruma o tamanho no padrão soudex
// 4 caracteres.
FixLength(result);
return result.ToString();
}
// -> Preenche com 0 nos casos menor q quatro,
// ou pega os 4 primeiros caracteres.
private static void FixLength(StringBuilder result)
{
var length = result.Length;
if (length < 4)
result.Append(new string('0', 4 - length));
else
result.Length = 4;
}
// -> Adiciona o caractere soudex, cuidando para não repetir.
private static void AddCharacter(StringBuilder result, char item)
{
if (result.Length == 0)
{
result.Append(item);
}
else
{
var code = GetSoundexDigit(item);
if (code != result[result.Length - 1].ToString())
result.Append(code);
}
}
// -> Algoritmo de substituição padrão Soudex.
private static string GetSoundexDigit(char item)
{
var charString = item.ToString();
if ("BFPV".Contains(charString))
return "1";
else if ("CGJKQSXZ".Contains(charString))
return "2";
else if ("DT".Contains(charString))
return "3";
else if ("L".Contains(charString))
return "4";
else if ("MN".Contains(charString))
return "5";
else if ("R".Contains(charString))
return "6";
else
return ".";
}
};
Pronto. Para testar coloque a chamada SQL no arquivo "Test.sql", veja:
"Dá o play macaco" #Cruj… E começa o deploy no banco de dados, veja o resultado:
Como podemos observar na imagem acima, o fonema é apresentado na janela de output, e como a dll já está instalada no SQL Server podemos testar diretamente nele:
Podemos aplicá-la em tabelas e views normalmente! O desempenho é muito superior comparada a uma implementação onde os dados são recuperados e processados por uma aplicação externa. Códigos .NET rodando diretamente na CLR do SQL Server são muito eficazes.
O Soundex é uma dos estudos disponíveis. Existe também o algoritmo BuscaBR, mas esse fica para uma outra oportunidade 
Obrigado, abraços.
1 comentários:
Cara adorei essa dica, porem somente um problema, ela é extremamente lenta na execussao em 4000 itens de uma tabela.
voce tem alguma ideia de como melhorar isso ?
abçs
Julio Fernandes
Postar um comentário