Material de Apoio – Especial – Outubro – 2013 – Fonética

Olá, bom dia.

Salve galera, mês de Outubro de 2013 começando, e para variar a minha cabeça esta fervendo, ainda mais que preciso produzir alguns artigos.

Pois bem, recentemente esta tendo a necessidade de utilizar recursos de Produção e Percepção de Sons no Microsoft SQL Server, algo que fosse possível expressão de maneira clara a chamada Fonética, mais conhecida como Ramo da Linguística. Para que você possa entender, vou destacar um breve resumo que encontrei no Wikipedia:

“A Fonética é o ramo da Linguística que estuda a natureza física da produção e da percepção dos sons da fala humana. Preocupa-se com a parte significante do signo linguístico e não com o seu conteúdo1 . Segundo Borba1 , subdivide-se em:

  • Fonética articulatória: estuda como os sons são produzidos, isto é, a posição e a função de cada um dos órgãos do aparelho fonador (língualábios, etc.);
  • Fonética acústica: analisa as características físicas dos sons da fala, ou seja, as ondas mecânicas produzidas e a sua percepção auditiva.”

————————————————————————————————————————————————————————————————————————————————————————

Uma pequena história

Perfeito, a muitos anos quando trabalhei com uma Linguagem de Programação Brasileira chamada GOL – Gerador OnLine de Programação, trata-se de uma mistura de Clipper, Cobol, criada especialmente para ambientes MS-Dos.

Esta linguagem apresentava em suas funcionalidades o uso da Fonética de uma forma fantástica, sinceramente até hoje eu não encontrei nada similar.

Voltando ao Assunto

Sempre tive esta curiosidade para entender como o Microsoft SQL Server desde a versão 2000 poderia trabalhar com a Fonética, foi ai que eu descobri a existência de uma Função chamada Soundex, que a princípio apresenta esta finalidade, mas que não conseguiu atingir as minhas expectativas.

Então, não contente comecei a buscar mais informações na Internet, e para minha alegria acabei lembrando de um Post antigo publicado nos Fóruns MSDN e TechNet do SQL Server que justamente ilustrava o que eu estava querendo.

Trata-se justamente deste conteúdo que eu vou compartilhar com vocês, dividido em seis partes:

Parte 1 – Função Fonetizar:

CREATE FUNCTION dbo.Func_Fonetizar (@STR VARCHAR(5000), @CONSULTA CHAR(1))
RETURNS VARCHAR(5000)
AS
BEGIN
DECLARE
@PARTICULA VARCHAR(5000),
@FONETIZADO VARCHAR(5000),
@FONETIZAR CHAR(1),
@AUX VARCHAR(5000),
@I INT,
@CONT INT,
@PREPOSICOES1 VARCHAR(1000),
@PREPOSICOES2 VARCHAR(1000),
@ALGROMANO1 VARCHAR(1000),
@NUMERO1 VARCHAR(1000),
@ALGROMANO2 VARCHAR(1000),
@NUMERO2 VARCHAR(1000),
@ALGROMANO3 VARCHAR(1000),
@NUMERO3 VARCHAR(1000),
@ALGARISMO VARCHAR(1000),
@ALGARISMOEXTENSO VARCHAR(1000),
@LETRAS VARCHAR(1000)

SELECT @STR = dbo.FUNC_REMOVE_ACENTO(@STR)

/*********************************************/
IF @STR = ‘ H ‘
SET @STR = ‘ AGA ‘

SELECT @STR = dbo.FUNC_SOMENTE_LETRAS(@STR)

/*ELIMINAR PALAVRAS ESPECIAIS*/
SELECT @STR = REPLACE(@STR,’ LTDA ‘,’ ‘)

/*ELIMINAR PREPOSICOES*/
SET @PREPOSICOES1 = ‘ DE DA DO AS OS AO NA NO ‘
SET @PREPOSICOES2 = ‘ DOS DAS AOS NAS NOS COM ‘

SET @I = 1

WHILE @I <= 32
BEGIN
SELECT @STR = REPLACE(@STR,SUBSTRING(@PREPOSICOES1,@I,4),’ ‘)
SET @I = @I + 4
END

SET @I = 1

WHILE @I <= 30
BEGIN
SELECT @STR = REPLACE(@STR,SUBSTRING(@PREPOSICOES2,@I,5),’ ‘)
SET @I = @I + 5
END

/*CONVERTE ALGARISMO ROMANO PARA NUMERO*/
SET @ALGROMANO1 = ‘ V I ‘
SET @NUMERO1 = ‘ 5 1 ‘
SET @I = 1
WHILE @I <= 6
BEGIN
SELECT @STR = REPLACE(@STR,SUBSTRING(@ALGROMANO1,@I,3),SUBSTRING(@NUMERO1,@I,3))
SET @I = @I + 3
END

SET @ALGROMANO2 = ‘ IX VI IV II ‘
SET @NUMERO2 = ‘ 9 6 4 2 ‘
SET @I = 1
WHILE @I <= 16
BEGIN
SELECT @STR = REPLACE(@STR,SUBSTRING(@ALGROMANO2,@I,4),SUBSTRING(@NUMERO2,@I+1,3))
SET @I = @I + 4
END

SET @ALGROMANO3 = ‘ VII III ‘
SET @NUMERO3 = ‘ 7 3 ‘
SET @I = 1
WHILE @I <= 10
BEGIN
SELECT @STR = REPLACE(@STR,SUBSTRING(@ALGROMANO3,@I,5),SUBSTRING(@NUMERO3,@I+2,3))
SET @I = @I + 5
END

SELECT @STR = REPLACE(@STR,’ X ‘,’ 10 ‘)
SELECT @STR = REPLACE(@STR,’ VIII ‘,’ 8 ‘)
/*CONVERTE NUMERO PARA LITERAL*/
SELECT @STR = REPLACE(@STR,’0′,’ZERO’)
SELECT @STR = REPLACE(@STR,’1′,’UM’)
SELECT @STR = REPLACE(@STR,’2′,’DOIS’)
SELECT @STR = REPLACE(@STR,’3′,’TRES’)
SELECT @STR = REPLACE(@STR,’4′,’QUATRO’)
SELECT @STR = REPLACE(@STR,’5′,’CINCO’)
SELECT @STR = REPLACE(@STR,’6′,’SEIS’)
SELECT @STR = REPLACE(@STR,’7′,’SETE’)
SELECT @STR = REPLACE(@STR,’8′,’OITO’)
SELECT @STR = REPLACE(@STR,’9′,’NOVE’)
/*********************************************/
/*ELIMINAR PREPOSICOES E ARTIGOS*/
SET @LETRAS = ‘ A B C D E F G H I J K L M N O P Q R S T U V X Z W Y ‘;

SET @I = 1
WHILE @I <= 78
BEGIN
SELECT @STR = REPLACE(@STR,SUBSTRING(@ALGROMANO2,@I,3),’ ‘)
SET @I = @I + 3
END
SET @STR = LTRIM(@STR)
SET @STR = RTRIM(@STR)

SET @PARTICULA = ”
SET @FONETIZADO = ”

SET @CONT = 1

WHILE @CONT <= LEN(@STR)+1
BEGIN
IF @CONT < LEN(@STR) + 1
BEGIN
IF SUBSTRING(@STR,@CONT,1) <> ‘ ‘
BEGIN
SET @PARTICULA = @PARTICULA + SUBSTRING(@STR,@CONT,1)
SET @FONETIZAR = ‘0’
END
ELSE
SET @FONETIZAR = ‘1’
END
ELSE
SET @FONETIZAR = ‘1’
IF @FONETIZAR = ‘1’
BEGIN
SELECT @PARTICULA = dbo.FUNC_FONETIZAR_PARTICULA(@PARTICULA)
SET @FONETIZADO = @FONETIZADO + ‘ ‘ + @PARTICULA
SET @PARTICULA = ”
END
SET @CONT = @CONT + 1
END

SET @FONETIZADO = LTRIM(@FONETIZADO)
SET @FONETIZADO = RTRIM(@FONETIZADO)
/*PREPARA A STRING PARA UM LIKE*/
IF @CONSULTA = ‘1’
BEGIN
SET @AUX = ‘%’

SET @I = 1

WHILE @I <= LEN(@FONETIZADO)
BEGIN
IF SUBSTRING(@FONETIZADO,@I,1) = ‘ ‘
SET @AUX = @AUX + ‘% %’
ELSE
SET @AUX = @AUX + SUBSTRING(@FONETIZADO,@I,1)
SET @I = @I + 1
END

IF SUBSTRING(@FONETIZADO,LEN(@FONETIZADO),1) <> ‘%’
SET @AUX = @AUX + ‘%’

SET @FONETIZADO = @AUX
END
IF @FONETIZADO = ”
SET @FONETIZADO = NULL

RETURN @FONETIZADO

END
GO

Parte 2 – Function – Fonetizar – Particula

CREATE FUNCTION dbo.FUNC_FONETIZAR_PARTICULA (@STR VARCHAR(5000))
RETURNS VARCHAR(5000)
AS
BEGIN
DECLARE
@AUX VARCHAR(5000),
@AUX2 VARCHAR(5000),
@I INT,
@N INT,
@J INT,
@LETRAS VARCHAR(26),
@CODFONETICO VARCHAR(26),
@CARACTERES VARCHAR(1000),
@CARACTERESSUB VARCHAR(1000)

/*CRIA A CODIFICAÇÃO DOS FONEMAS*/
SET @LETRAS = ‘ABPCKQDTEIYFVWGJLMNOURSZX9’
SET @CODFONETICO = ‘123444568880AABCDEEGAIJJL9’

/*ELIMINA LETRAS IGUAIS SEGUIDAS UMA DA OUTRA*/

SET @AUX = SUBSTRING(@STR,1,1) /*RECEBE A PRIMEIRA LETRA*/
SET @I = 2

WHILE @I <= LEN(@STR)
BEGIN
IF SUBSTRING(@STR,@I – 1,1) <> SUBSTRING(@STR,@I,1)
SET @AUX = @AUX + SUBSTRING(@STR,@I,1)
SET @I = @I + 1
END

/*IGUALA FONEMAS PARECIDOS*/
IF SUBSTRING(@AUX,1,1) = ‘W’
IF SUBSTRING(@AUX,2,1) = ‘I’
SET @AUX = ‘U’ + SUBSTRING(@AUX,2,LEN(@AUX)) /*TROCA W POR U*/
ELSE
IF SUBSTRING(@AUX,2,1) IN (‘A’,’E’,’O’,’U’)
SET @AUX = ‘V’ + SUBSTRING(@AUX,2,LEN(@AUX)) /*TROCA W POR V*/
SELECT @AUX = dbo.FUNC_SUBSTITUI_TERMINACAO(@AUX)

SET @CARACTERES = ‘TSCHSCH TSH TCH SH CH LH NH PH GN MN SCE SCI SCY CS KS PS TS TZ XS CE CI CY GE GI GY GD CK PC QU SC SK XC SQ CT GT PT ‘
SET @CARACTERESSUB = ‘XXXXXXX XXX XXX XX XX LI NN FF NN NN SSI SSI SSI SS SS SS SS SS SS SE SI SI JE JI JI DD QQ QQ QQ SQ SQ SQ 99 TT TT TT ‘

SET @I = 1
WHILE @I <= 148
BEGIN
SELECT @AUX = REPLACE(@AUX,LTRIM(RTRIM(SUBSTRING(@CARACTERES,@I,4))), LTRIM(RTRIM(SUBSTRING(@CARACTERESSUB,@I,4))))
SET @I = @I + 4
END

/*TRATAR CONSOANTES MUDAS*/
SELECT @AUX = dbo.FUNC_TRATA_CONSOANTE_MUDA(@AUX,’B’,’I’)
SELECT @AUX = dbo.FUNC_TRATA_CONSOANTE_MUDA(@AUX,’D’,’I’)
SELECT @AUX = dbo.FUNC_TRATA_CONSOANTE_MUDA(@AUX,’P’,’I’)

— TRATA LETRAS
— RETIRA LETRAS IGUAIS
IF SUBSTRING(@AUX,1,1) = ‘H’
BEGIN
SET @AUX2 = SUBSTRING(@AUX,2,1) — RECEBE A SEGUNDA LETRA
SET @J = 3
END
ELSE
BEGIN
SET @AUX2 = SUBSTRING(@AUX,1,1) — RECEBE A PRIMEIRA LETRA
SET @J = 2
END

WHILE @J <= LEN(@AUX)
BEGIN
IF (((SUBSTRING(@AUX,@J – 1,1) <> SUBSTRING(@AUX,@J,1))) AND
(SUBSTRING(@AUX,@J,1) <> ‘H’))
SET @AUX2 = @AUX2 + SUBSTRING(@AUX,@J,1)
SET @J = @J + 1
END

SET @AUX = @AUX2

— TRANSFORMA LETRAS EM CODIGOS FONETICOS
SET @AUX2 = ”

SET @I = 1

WHILE @I <= LEN(@AUX)
BEGIN
SET @N = 1
WHILE @N <= 26
BEGIN
IF SUBSTRING(@AUX,@I,1) = SUBSTRING(@LETRAS,@N,1)
SET @AUX2 = @AUX2 + SUBSTRING(@CODFONETICO,@N,1)
SET @N = @N + 1
END
SET @I = @I + 1
END

RETURN @AUX2
END
GO

Parte 3 – Function – Remove Acento

CREATE FUNCTION dbo.FUNC_REMOVE_ACENTO ( @TEXTO VARCHAR (5000))
RETURNS VARCHAR (5000)
AS
BEGIN
DECLARE @COMACENTOS VARCHAR(50)
DECLARE @SEMACENTOS VARCHAR(50)
DECLARE @QTD_TEXTO INT
DECLARE @CONTADOR INT
DECLARE @QTD INT
DECLARE @CONT INT
DECLARE @CONT_C INT
DECLARE @LETRA_T NVARCHAR(1)
DECLARE @LETRA_C NVARCHAR(1)
DECLARE @RESULTADO VARCHAR(5000)

SET @COMACENTOS = ‘áÁàÀâÂãÃéÉèÈêÊíÍìÌîÎóÓòÒôÔõÕúÚùÙûÛüÜçÇ’
SET @SEMACENTOS = ‘AAAAAAAAEEEEEEIIIIIIOOOOOOOOUUUUUUUUCC’
SET @QTD_TEXTO = (SELECT LEN(@TEXTO))
SET @CONTADOR = 0
SET @RESULTADO = ”

INICIO:
WHILE @CONTADOR < @QTD_TEXTO
BEGIN
SET @CONTADOR = @CONTADOR + 1
SET @LETRA_T = (SELECT SUBSTRING(@TEXTO,@CONTADOR,1))
SET @CONT = (SELECT LEN(@COMACENTOS))
SET @QTD = 0
WHILE @QTD < @CONT
BEGIN
SET @QTD = @QTD + 1
SET @LETRA_C = (SELECT SUBSTRING(@COMACENTOS,@QTD,1))
IF @LETRA_C = @LETRA_T
BEGIN
SET @RESULTADO = @RESULTADO + (SELECT SUBSTRING(@SEMACENTOS,@QTD,1))
GOTO INICIO
END
ELSE
BEGIN
IF @QTD = @CONT
SET @RESULTADO = @RESULTADO + @LETRA_T
END
END
END

RETURN ( UPPER(@RESULTADO) )
END
GO

Parte 4 – Function – Somente – Letras

CREATE FUNCTION dbo.FUNC_SOMENTE_LETRAS ( @TEXTO VARCHAR (5000))
RETURNS VARCHAR (5000)
AS
BEGIN
DECLARE @LETRAS VARCHAR(28)
DECLARE @QTD_TEXTO INT
DECLARE @CONTADOR INT
DECLARE @QTD INT
DECLARE @CONT INT
DECLARE @CONT_C INT
DECLARE @LETRA_ANT NVARCHAR(1)
DECLARE @LETRA_T NVARCHAR(1)
DECLARE @LETRA_C NVARCHAR(1)
DECLARE @RESULTADO VARCHAR(5000)
SET @LETRAS = ‘ABCDEFGHIJKLMNOPQRSTUVXZWY ‘;

SET @QTD_TEXTO = (SELECT LEN(@TEXTO)) + 1
SET @CONTADOR = 0
SET @RESULTADO = ”
SET @LETRA_ANT = (SELECT SUBSTRING(@TEXTO,1,1))
INICIO:
WHILE @CONTADOR < @QTD_TEXTO
BEGIN
SET @CONTADOR = @CONTADOR + 1
SET @LETRA_T = (SELECT SUBSTRING(@TEXTO,@CONTADOR,1))
SET @CONT = (SELECT LEN(@LETRAS)) + 1
SET @QTD = 0
WHILE @QTD < @CONT
BEGIN
SET @QTD = @QTD + 1
SET @LETRA_C = (SELECT SUBSTRING(@LETRAS,@QTD,1))
IF @LETRA_C = @LETRA_T
BEGIN
IF @LETRA_ANT = ‘ ‘
AND @LETRA_T = ‘ ‘
BEGIN
GOTO INICIO
END
ELSE
BEGIN
SET @RESULTADO = @RESULTADO + (SELECT SUBSTRING(@LETRAS,@QTD,1))
SET @LETRA_ANT = @LETRA_T
GOTO INICIO
END
END
END
END

RETURN ( UPPER(@RESULTADO) )

END
GO

Parte 5 – Substitui – Terminação

CREATE FUNCTION dbo.FUNC_SUBSTITUI_TERMINACAO (@STR VARCHAR(5000))
RETURNS VARCHAR(5000)
AS
BEGIN
DECLARE
@TERMINACAO VARCHAR(1000),
@TERMINACAOSUB VARCHAR(1000),
@TAMANHOMINSTR VARCHAR(1000),
@I INT

/*
SUBSTITUI AS TERMINAÇÕES CONTIDAS NO VETOR $vTerminacao PELAS DO VETOR $vTerminacaoSub RESPEITANDO
O TAMANHO MÍNIMO DA STRING CONTIDO NO VETOR $vTamanhoMinStr.
NO CASO DA STRING TERMINAR COM ‘N’, O LAÇO CONTINUARÁ, POIS PODERÃO EXIXTIR NOVAS SUBSTITUIÇÕES COM
A TERMINAÇÃO ‘M’.
*/
SELECT @STR = dbo.FUNC_REMOVE_ACENTO(@STR)

SET @TERMINACAO = ‘N B D T W AM OM OIMUIMCAOAO OEMONSEIAX US TH’
SET @TERMINACAOSUB = ‘M N N N N SSNN N N IA IS OS TI’
SET @TAMANHOMINSTR = ‘2 3 3 3 3 2 2 2 2 3 2 2 2 2 2 2 3’
SET @I = 1

LOOP:
BEGIN

IF RTRIM(SUBSTRING(@STR,LEN(@STR) – LEN(SUBSTRING(@TERMINACAO,@I,3)) + 1,LEN(SUBSTRING(@TERMINACAO,@I,3)))) = RTRIM(SUBSTRING(@TERMINACAO,@I,3)) AND
(LEN(@STR) >= CONVERT(INT,SUBSTRING(@TAMANHOMINSTR,@I,3)))
BEGIN
SET @STR = SUBSTRING(@STR,1,LEN(@STR) – LEN(SUBSTRING(@TERMINACAO,@I,3))) + RTRIM(SUBSTRING(@TERMINACAOSUB,@I,3))
IF @I > 1
GOTO FIM
END

SET @I = @I + 3

IF @I < 52
GOTO LOOP

END

FIM:
BEGIN
RETURN @STR
END

END
GO

Parte 6 – Function – Trata – Consoante

CREATE FUNCTION dbo.FUNC_TRATA_CONSOANTE_MUDA (@STR VARCHAR(5000), @CONSOANTE CHAR(1), @COMPLEMENTO CHAR(1))
RETURNS VARCHAR(5000)
AS
BEGIN
DECLARE
@I INT,
@CONTADOR INT

/*
PARA TODAS AS OCORRÊNCIAS DA CONSOANTE $consoante QUE NÃO ESTIVER SEGUIDA DE VOGAL, SERÁ ADICIONADO
O CONTEÚDO DA VARIÁVEL $complemento NA STRING.
*/

SET @I = LEN(@STR)
SET @CONTADOR = 1

LOOP:
BEGIN

— for i in 1..length(tStr) loop
IF ((SUBSTRING(@STR,@CONTADOR,1) = @CONSOANTE) AND
(SUBSTRING(@STR,@CONTADOR + 1,1) NOT IN (‘A’,’E’,’I’,’O’,’U’)))

SET @STR = SUBSTRING(@STR,1,@CONTADOR) + @COMPLEMENTO + SUBSTRING(@STR,@CONTADOR + 1,LEN(@STR))

SET @CONTADOR = @CONTADOR + 1

IF @CONTADOR <= @I
GOTO LOOP

END

RETURN @STR

END
GO

Créditos – Koiti – http://social.msdn.microsoft.com/Forums/en-US/03c2b5a8-8193-4ee8-8515-1823d5001f95/busca-fonetica-no-sql-server-2005-sem-o-uso-do-soundex

Espero que este material possa te ajudar, agradeço mais uma vez a sua visita ao meu blog.

Fique a vontade para deixar seus comentários, dúvidas, sugestões, críticas ou opiniões.

Nos encontramos em breve.

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

9 comentários em “Material de Apoio – Especial – Outubro – 2013 – Fonética”

  1. Oi Pedro, bom dia.

    Estou testando esse código, parece-me que ele foi alterado em relação ao original (do Kleber).

    Após corrigir alguns erros de compilação devido ao HTML da página, os resultados não foram satisfatórios.

    Tenho duas perguntas.
    * Você testou o código que disponibilizou aqui? Os testes foram satisfatórios?
    * Você possui o código original do Kleber?

    Abraços,
    BRuno

    Curtir

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s