Retirar acentuação e caracteres “invisíveis” no SQL Server

p023_cotacaoÀs vezes é necessário retirar a acentuação em textos que existem no banco de dados, por motivos variados. Ou então remover caracteres de controle, como salto de linha (LF, line feed), retorno de carro (CR, carriage return), tabulação vertical etc.
Neste artigo são demonstrados alguns métodos, desde o mais tradicional (varredura caractere a caractere) até o uso da função TRANSLATE.

1. Introdução

Embora não comum, às vezes é necessário retirar a acentuação de dados que existem no banco de dados. Isso pode ocorrer, por exemplo, em processos de migração de sistemas ERP (de um que permita acentuação para outro que não) ou até mesmo em processos de BI, quando se reúnem dados de diversas fontes e é necessário homogeneizar os dados. Além dos caracteres acentuados, há também a questão dos caracteres de controle, às vezes denominados de ocultos, invisíveis ou não imprimíveis.

Os caracteres de controle vêm da época inicial da telecomunicação e eram (e a maioria ainda são) utilizados como comandos remotos de dispositivos. Por exemplo, para fazer com que o teletipo movesse o cabeçote de impressão para o início da linha e que avançasse uma linha eram enviados os caracteres carriage return (CR) e line feed (LF) junto ao texto.

Cada caractere de controle ocupa um único byte. Na mensagem seguinte, para permitir a visualização foram utilizadas siglas do caracteres de controle e delimitados por «»:

«BEL»«STX»AS COTACOES SAO AS SEGUINTES:«CR»«LF»1. FEIJAO 180«CR>«LF»2. ARROZ 120«CR»«LF».«ETX»«EOT»

temos o envio do caractere de controle BEL que faz com que a campainha do teletipo toque (alertando o operador do equipamento remoto para nova mensagem a ser recebida, por exemplo), a seguir o texto da mensagem entremeado por pares CR+LF para posicionar o cabeçote de impressão no início da linha seguinte e outros, cujo significado você pode consultar na tabela ASCII. Ao final, o seguinte texto é impresso no teletipo:

AS COTACOES SAO AS SEGUINTES:
1. FEIJAO 180
2. ARROZ 120
.

Os teletipos foram substituídos pelos terminais burros de vídeo e pelas impressoras matriciais, mas ambos continuaram a utilizar os caracteres de controle ASCII. Originalmente a tabela ASCII utilizava bytes de 7 bits, o que permitia a definição de 128 caracteres diferentes, entre caracteres de controle e texto. Posteriormente passaram a trafegar em bytes de 8 bits, possibilitando a definição de 256 caracteres. A utilização desse oitavo bit varia de acordo com o padrão utilizado e isso gerou uma confusão enorme quando a Microsoft utilizou esse oitavo bit para definir diferentes conjuntos de caracteres, diferenciando-os pelo code page.

Dentre os caracteres de controle, os frequentes em colunas de texto (geralmente oriundos de telas com campo de observação) são CR, LF e HT. Os dois primeiros são gerados quando se pressiona a tecla ENTER e o último ao pressionar a tecla TAB.

TRANSLATE. Em recente artigo que publiquei, “Novos recursos de programação para SQL Server”, um dos itens foi sobre a função TRANSLATE, implementada na versão 2017 do SQL Server. Nesse item foi citado que “Uma aplicação para a função TRANSLATE() é a retirada de acentos”, com uma rápida demonstração de como retirar acentuação. A partir daquela demonstração de aplicação veio a ideia de escrever artigo específico sobre essa aplicação da função TRANSLATE, fazendo então parte da série “Novos recursos em T-SQL”.

Ah! Ao pesquisar sobre acentuação me deparei com a informação de que o til ~ não é um acento mas sim um sinal de nasalização

2. Métodos

Neste artigo serão descritos quatro métodos de retirar acentuação: a varredura caractere a caractere; uso de chamadas em sequência da função REPLACE; a utilização de COLLATE; e o uso da função nativa TRANSLATE.

2.1. Varredura caractere a caractere

Uma das formas de implementar este método é através da utilização de laço com WHILE para extrair cada caractere do texto e então compará-lo com um vetor de caracteres acentuados. Se for detectado que o caractere é acentuado, então ocorre a substituição pelo respectivo caractere, sem acentuação. Além dos caracteres acentuados, a função tem a opção de verificar a presença de caracteres invisíveis, removendo-os ou substituindo-os por espaço.

O método foi encapsulado em uma função de usuário do tipo escalar:

-- código #V.1 v3
CREATE FUNCTION dbo.RetiraAcento_V (
     @texto varchar(8000),
     @modo tinyint    
     -- 0: somente retira a acentuação
     -- 1: somente remove caracteres de controle
     -- 2: somente substitui caracteres de controle por espaço
     -- 3: retira a acentuação e 
     --            substitui caracteres de controle por espaço
)
returns varchar(8000)

begin
-- validação dos parâmetros
IF @texto is null
   or @modo is null
   or not @modo between 0 and 3
  return NULL;   

--
declare @ComAcento varchar(60), @SemAcento varchar(60);
set @ComAcento= 'áéíóúÁÉÍÓÚàÀãõÃÕâêôÂÊÔçÇ';
set @SemAcento= 'aeiouAEIOUaAaoAOaeoAEOcC';

declare @novo_texto varchar(8000), @tam_texto int, @ponteiro int;
set @tam_texto= len (@texto);
set @novo_texto= space(0);

declare @caractere char(1);
declare @novo_caractere varchar(1), @alterado tinyint;

set @ponteiro= 1;
while (@ponteiro <= @tam_texto)
  begin
  set @alterado= 0;

  -- obtém o caractere
  set @caractere= substring (@texto, @ponteiro, 1);

  IF (@modo > 0)
    -- tratamento de caractere de controle
    begin
    IF ((ascii (@caractere) < 32) or (ascii (@caractere) = 127))
      begin
      IF @modo = 1
        set @novo_caractere= space (0)
      else
      IF @modo in (2, 3)
        set @novo_caractere= space (1);
      set @alterado= 1;
      end;
    end;

  -- verifica se é acentuado
  IF (@modo in (0, 3)) and (@alterado = 0)
    IF (charindex (@caractere, @ComAcento) > 0)
      begin
      set @novo_caractere= substring (@SemAcento,
                              charindex (@caractere, @ComAcento), 1);
      set @alterado= 1;
      end;

  --
  IF (@alterado = 1)
    set @novo_texto+= @novo_caractere
  else
    set @novo_texto+= @caractere;
 
  -- próximo caractere
  set @ponteiro+= 1;
  end;

return @novo_texto;
end;
go

A função possui dois parâmetros:

  1. @texto: o texto a ser analisado;
  2. @modo: a ação a ser executada.

O segundo parâmetro pode ter os valores:

  • 0: somente retira a acentuação;
  • 1: somente remove caracteres de controle;
  • 2: somente substitui caracteres caracteres de controle por espaço; e
  • 3: retira a acentuação e substitui caracteres caracteres de controle por espaço.

Um exemplo de utilização da função:

-- código #V.2
declare @Texto varchar(200);
set @Texto= 'Retirar acentuação' + char(13) 
             + 'é o objetivo desta função';

SELECT @Texto as [Texto original],
       dbo.RetiraAcento_V (@Texto, 0) as [Texto tratado];

obtendo-se o seguinte resultado:

p023_codigo #V.2

Outra forma de implementar o método de varredura caractere a caractere é através do uso de um recurso que não é muito conhecido, tabela auxiliar de números, possibilitando assim a quebra do texto em suas partes individuais – caractere – e então realizar operações relacionais comparando cada caractere com uma tabela de substituição de caracteres acentuados. Como o objetivo deste artigo é o de apresentar a função TRANSLATE como nova solução para retirada de acentuação, não prolongarei na explicação das várias formas de implementar a varredura caractere a caractere.

2.2. REPLACE

Outra abordagem que se verifica em artigos e fóruns é o uso da função REPLACE para a substituição, caractere a caractere. Para cada caractere a ser substituído utiliza-se uma chamada à função REPLACE, com uma chamada dentro da outra.

A abordagem foi encapsulada em uma função de usuário do tipo escalar:

-- código #R.1 v3
CREATE FUNCTION dbo.RetiraAcento_R (
     @texto varchar(8000),
     @modo tinyint    
     -- 0: somente retira a acentuação
     -- 1: somente remove caracteres de controle
     -- 2: somente substitui caracteres de controle por espaço
     -- 3: retira a acentuação e 
     --            substitui caracteres de controle por espaço
)
returns varchar(8000)

begin
-- validação dos parâmetros
IF @texto is null
   or @modo is null
   or not @modo between 0 and 3
  return NULL;

declare @novo_texto varchar(8000), @substituto varchar(1);
set @novo_texto= @texto;

-- tratamento de caracteres de controle
IF (@modo > 0)
  begin
  -- define substituto
  IF (@modo in (2, 3))
    set @substituto= space(1)
  else
    set @substituto= space(0);
   
  -- efetua substituição
  set @novo_texto= replace (replace (replace (replace (replace (
                   replace (replace (replace (replace (replace (
                   replace (@novo_texto,
                            char(1), @substituto),
                            char(2), @substituto),
                            char(3), @substituto),
                            char(4), @substituto),
                            char(9), @substituto), -- HT ou TAB
                            char(10), @substituto), -- LF
                            char(13), @substituto), -- CR
                            char(29), @substituto), -- GS
                            char(30), @substituto), -- RS
                            char(31), @substituto), -- US
                            char(127), @substituto); -- DEL
  end;
 
-- tratamento de acentuação
IF (@modo in (0, 3))
  set @novo_texto= replace (replace (replace (replace (replace (
                   replace (replace (replace (replace (replace (
                   replace (replace (replace (replace (replace (
                   replace (replace (replace (replace (replace (
                   replace (replace (replace (
                   replace (@novo_texto,
                            'á', 'a'),  -- acento agudo
                            'é', 'e'),
                            'í', 'i'),
                            'ó', 'o'),
                            'ú', 'u'),
                            'Á', 'A'),
                            'É', 'E'),
                            'Í', 'I'),
                            'Ó', 'O'),
                            'Ú', 'U'),
                            'à', 'a'),  -- crase
                            'À', 'A'),
                            'ã', 'a'),  -- til
                            'õ', 'o'),
                            'Ã', 'A'),  
                            'Õ', 'O'),
                            'â', 'a'),  -- acento circunflexo
                            'ê', 'e'),
                            'ô', 'o'),
                            'Â', 'A'),
                            'Ê', 'E'),
                            'Ô', 'O'),
                            'ç', 'c'),  --
                            'Ç', 'c');

return @novo_texto;
end;
go

A função possui dois parâmetros:

  1. @texto: o texto a ser analisado;
  2. @modo: a ação a ser executada.

O segundo parâmetro pode ter os valores:

  • 0: somente retira a acentuação;
  • 1: somente remove caracteres de controle;
  • 2: somente substitui caracteres caracteres de controle por espaço; e
  • 3: retira a acentuação e substitui caracteres caracteres de controle por espaço.

Para fins de demonstração, no código SQL da função somente foram tratados alguns caracteres de controle. Entretanto, o código SQL completo envolve tratamento de char(0) até char(31).

Um exemplo de utilização da função:

-- código #R.2
declare @Texto varchar(200);
set @Texto= 'Retirar acentuação' + char(13) 
             + 'é o objetivo desta função';

SELECT @Texto as [Texto original],
       dbo.RetiraAcento_R (@Texto, 0) as [Texto tratado];

obtendo-se o mesmo resultado do código #V.2.

2.3. COLLATE

Quando conheci este método, achei-o engenhoso. Ele consta em vários artigos e fóruns em português mas sem informar o autor, sendo que a utilização mais antiga que encontrei foi no blog de Wolney Maia, no artigo publicado em 2008: Como retirar acento de caracteres.

-- código #C.1
declare @Texto varchar(200);
set @Texto= 'Retirar acentuação' + char(13) 
             + 'é o objetivo deste método';

SELECT @Texto as [Texto original],
       @Texto collate sql_latin1_general_cp1251_cs_as as [Texto tratado];

Deixo por conta do artigo do Wolney a explicação sobre o uso desse método.

2.4. TRANSLATE

E finalmente a cereja do bolo. Disponível a partir da versão 2017 do SQL Server, a função TRANSLATE() permite a transliteração (substituição de caracteres) de um texto, utilizando vetores de tradução caractere a caractere.

A sintaxe é a seguinte:

TRANSLATE ( inputString, characters, translations)

onde

  • inputString: contém o texto em que ocorrerão as substituições;
  • characters: contém o conjunto de caracteres que serão substituídos;
  • translations: contém o conjunto de caracteres que serão utilizados para a substituição.

O conjunto de caracteres translations deve ter o mesmo tamanho que o conjunto de caracteres characters, pois há uma relação de 1:1 entre os dois conjuntos, sendo que as ocorrências em inputString do primeiro caractere de characters serão substituídas pelo primeiro caractere de translations e assim em diante. Múltiplas ocorrências, múltiplas substituições.

O retorno da função é do mesmo tipo e tamanho que o parâmetro inputString.

Como exemplo de uso, o caso em que vogais são substituídas por algarismos, geralmente em processos de criação de senhas:

-- código #T.1
declare @Texto varchar(200);
set @Texto= 'Texto a ser transliterado';

SELECT @Texto as [Texto original],
       translate (@Texto, 'aeio', '@310') as [Texto transliterado];

Ao rodar o código SQL anterior, temos como resultado:

p023_codigo #T.1

Considerando-se a combinação de vogais e acentos válidos no idioma português, eis uma forma de retirar os acentos:

-- código #T.2 v2
declare @Texto varchar(200), 
        @ComAcento varchar(50), @SemAcento varchar(50);

set @ComAcento= 'áéíóúÁÉÍÓÚàÀãõÃÕâêôÂÊÔçÇ';
set @SemAcento= 'aeiouAEIOUaAaoAOaeoAEOcC';
set @Texto= 'Retirar acentuação é o objetivo deste teste';

SELECT @Texto as [Texto original],
       translate (@Texto, @ComAcento, @SemAcento) as [Texto tratado];

Ao rodar o código SQL anterior, temos como resultado:

p023_codigo #T.2

E caso se queira também verificar a presença de caracteres invisíveis, removendo-os ou substituindo por outro caractere? A solução também é simples, envolvendo ligeira alteração na montagem dos vetores @ComAcento e @SemAcento. Mas neste caso o processo fica mais simples de utilizar ao se criar uma função de usuário.

-- código #T.3 v3
CREATE FUNCTION dbo.RetiraAcento_T (
     @texto varchar(8000),
     @modo tinyint 
     -- 0: somente retira a acentuação
     -- 1: somente remove caracteres de controle
     -- 2: somente substitui caracteres de controle por espaço
     -- 3: retira a acentuação e
     --            substitui caracteres de controle por espaço
)
returns varchar(8000)

begin
-- validação dos parâmetros
IF @texto is null
   or @modo is null
   or not @modo between 0 and 3
  return NULL;

-- vetores de substituição
declare @I int, @ComAcento varchar(80), @SemAcento varchar(80);
set @ComAcento= space(0);
set @SemAcento= space(0);

-- tratamento de caracteres de controle
IF (@modo > 0)
  begin
  set @I= 0;
  while (@I <= 31)
    begin
    set @ComAcento+= char(@I);
    set @SemAcento+= IIF ((@modo = 1), char(127), space(1));
    set @I+= 1;
    end;
  set @ComAcento+= char(127);
  set @SemAcento+= IIF ((@modo = 1), char(127), space(1));
  end;

-- tratamento de acentuação
IF (@modo in (0, 3))
  begin
  set @ComAcento+= 'áéíóúÁÉÍÓÚàÀãõÃÕâêôÂÊÔçÇ';
  set @SemAcento+= 'aeiouAEIOUaAaoAOaeoAEOcC';
  end;

--
declare @novo_texto varchar(8000);
set @novo_texto= translate (@texto, @ComAcento, @SemAcento);

-- remove caracteres de controle
IF (@modo = 1)
  set @novo_texto= replace (@novo_texto, char(127), space (0));

return @novo_texto;
end;
go

Atenção: o código SQL acima utiliza o caractere DEL como substituto temporário de caracteres invisíveis, para remoção ao final da função.

Um exemplo de utilização:

-- código #T.4
declare @Texto varchar(200);
set @Texto= 'Retirar acentuação' + char(13) 
             + 'é o objetivo desta função';

SELECT @Texto as [Texto original],
       dbo.RetiraAcento_T (@Texto, 0) as [Texto tratado];

obtendo-se o seguinte resultado:

p023_codigo #V.2

Ao encapsular a função TRANSLATE em uma função de usuário escalar pode-se perder em performance, principalmente pela necessidade de montar os vetores de acentuação a cada execução da função. Uma forma de tirar o máximo proveito da função TRANSLATE é criar procedimento que monte previamente os vetores @ComAcento e @SemAcento:

-- código #T.5 v3
CREATE PROCEDURE dbo.MontaVetorAcento
     @modo tinyint = 0,
     @vComAcento varchar(80) output,
     @vSemAcento varchar(80) output
as

begin
-- validação dos parâmetros
IF @modo is null
   or not @modo between 0 and 3
  return -1;

-- vetores de substituição
declare @I int;
set @vComAcento= space(0);
set @vSemAcento= space(0);

-- tratamento de caracteres de controle
IF (@modo > 0)
  begin
  set @I= 0;
  while (@I <= 31)
    begin
    set @vComAcento+= char(@I);
    set @vSemAcento+= IIF ((@modo = 1), char(127), space(1));
    set @I+= 1;
    end;
  set @vComAcento+= char(127);
  set @vSemAcento+= IIF ((@modo = 1), char(127), space(1));
  end;

-- tratamento de acentuação
IF (@modo in (0, 3))
  begin
  set @vComAcento+= 'áéíóúÁÉÍÓÚàÀãõÃÕâêôÂÊÔçÇ';
  set @vSemAcento+= 'aeiouAEIOUaAaoAOaeoAEOcC';
  end;
end;
go

e a seguir utilizar a função TRANSLATE diretamente.

Supondo a existência de tabela ATENDIMENTO com milhões de linhas e que nela exista a coluna OBSERVACAO, contendo acentuação e caracteres de controle, onde deve-se retirar a acentuação e também substituir caracteres de controle por espaço. Para evitar o uso de função de usuário escalar pode-se então utilizar o procedimento MontaVetorAcento em conjunto com a função TRANSLATE:

-- código #T.6
declare @ComAcento varchar(80), @SemAcento varchar(80);

EXECUTE dbo.MontaVetorAcento 0, @ComAcento output, @SemAcento output;

SELECT OBSERVACAO as [Observação original],
       translate (OBSERVACAO, @ComAcento, @SemAcento) as [Observação sem acentos]
  from ATENDIMENTO;

Geralmente o processo de substituição de caracteres de controle é um pouco mais complexa. Por exemplo, no caso de CR (carriage return), pode-se substituí-lo por “;” enquanto outros podem ser removidos do texto. Estes detalhes podem ser tratados no procedimento MontaVetorAcento e com o uso combinado de REPLACE e TRANSLATE, sem o uso de funções de usuário do tipo escalar, que para grandes volumes de dados podem degradar a performance.

3. Observações

Como citado na introdução, o objetivo deste artigo foi o de apresentar uma aplicação para a função TRANSLATE, que ainda é pouco utilizada por ter sido implementada somente a partir da versão 2017 do SQL Server.

Nos exemplos dos métodos foram criadas funções de usuário do tipo escalar. Como se sabe, este tipo de função de usuário pode degradar a performance quando se manipulam grandes volumes de dados. A partir da versão 2017 CU3 do SQL Server é possível acompanhar nos planos de execução as estatísticas de utilização de função de usuário; detalhes no artigo “More Showplan enhancements – UDF”. O recurso foi posteriormente implementado na atualização SP2 da versão 2016. Entretanto, cabe destacar que a partir da versão 2019 do SQL Server, e desde que o banco de dados esteja com nível de compatibilidade 150 (ou maior), as funções de usuário do tipo escalar podem ter sua performance melhorada ao atenderem a algumas condições. Os detalhes estão na documentação: Scalar UDF Inlining.

Habitualmente nos artigos eu acrescento os planos de execução dos códigos SQL, para se ter uma visualização do impacto das diferentes soluções. Como este artigo não tem o objetivo de comparar soluções, não há análise de planos de execução e nem de performance.

Espero que este artigo seja útil para disseminar o uso da função TRANSLATE.

4. Fontes de consulta

4.1. Documentação

4.2. Artigos

4.3. Glossário

PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo.

Retirando acentuação (e caracteres “invisíveis”) com a função TRANSLATE

Deixe um comentário

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 )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Crie seu site com o WordPress.com
Comece agora
%d blogueiros gostam disto: