Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Utilizando TRANSLATE para substituir vários REPLACE

Visualizações: 12.656 views
Tempo de Leitura: 6 minutos

Olá pessoal!
Nesse post rápido aqui, gostaria de compartilhar com vocês alguns casos de uso da função TRANSLATE, disponível desde o SQL Server 2017, que pode ajudar a substituir vários comandos de REPLACE com apenas um comando TRANSLATE, reduzindo bastante a complexidade e tamanho dos códigos.

Spoiler: Usa uma versão antes da 2017? Calma.. Existe jeito pra tudo 🙂

Para entender como o comando TRANSLATE funciona e suas diferenças pro REPLACE, preparei alguns exemplos legais para vocês.

TRANSLATE Simples

Nesse primeiro exemplo, vamos demonstrar como o TRANSLATE funciona na prática e como ter o mesmo comportamento utilizando o REPLACE:

Resultado:

Observação: No exemplo acima, utilizei o TRANSLATE para remover caracteres especiais de uma string. Você também pode fazer isso utilizando outras opções, como as que compartilhei nos artigos abaixo:

TRANSLATE com variáveis

Nesse outro exemplo, quero mostrar que é possível utilizar o TRANSLATE com variáveis. Vou mostrar também, uma diferença no comportamento do TRANSLATE comparado ao REPLACE.

Resultado:

No exemplo acima, ficou bem claro o quanto o TRANSLATE facilita a escrita e a leitura do código, evitando erros de digitação, reduzindo a complexidade e facilitando a manutenção e leitura. Mas os 2 apresentaram o mesmo resultado. E eu queria que não tivesse esse espaçamento entre os caracteres, como no print acima.

Vamos tentar remover isso.

No REPLACE foi bem fácil. Bastou trocar o caractere de espaço ” ” por uma string vazia “”:

Resultado:

Agora vou tentar utilizando o TRANSLATE:

Resultado:

Msg 9828, Level 16, State 1, Line 7
The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

Ou seja, não consigo fazer a mesma coisa com o TRANSLATE nesse caso, porque o tamanho da string a ser localizada e a string a ser substituída devem ser do mesmo tamanho. Por mais que eu esteja utilizando o REPLICATE para forçar o tamanho a ser igual, como a string é vazia (“”), a quantidade de caracteres não aumenta.

Esse exemplo então, justifica o uso do REPLACE em determinados cenários quando você quer substituir um caractere por uma string vazia, correto?

Calma.. Podemos tentar um “workaround” para resolver isso, mesclando um TRANSLATE com um único REPLACE, onde irei substituir todos os caracteres especiais pelo caractere nulo (caractere 0) utilizando o TRANSLATE e depois eu troco todos os nulos por string vazia com um único REPLACE:

Resultado:

TRANSLATE + SELECT em tabelas

O comando TRANSLATE também funciona normalmente se aplicado a conjuntos de dados, como tabelas e views:

Resultado:

No exemplo acima, utilizei o TRANSLATE para remover números de uma string. Você também pode fazer isso utilizando outras opções, como as que compartilhei nos artigos abaixo:

TRANSLATE nas versões anteriores ao 2017

Como eu menciono no começo do post, a função TRANSLATE só está disponível a partir do SQL Server 2017, ou seja, se você precisa utilizá-la em versões anteriores, você terá que se contentar com vários comandos de REPLACE aninhados, certo? NÃO!!

Vou compartilhar com vocês essa função que encontrei nesse post aqui do StackOverflow e fiz umas pequenas modificações para não precisar criar tabelas externas.

A utilização é igual a da função original:

Resultado:

Importante: De modo geral, funções de usuário (UDF) podem causar grandes problemas de performance, especialmente se utilizados na cláusula WHERE/JOIN e em tabelas muito grandes. Para saber mais sobre isso, leia o meu post SQL Server – Utilizando colunas calculadas (ou colunas computadas) para Performance Tuning.

Outra dica: De modo geral, funções de usuário (UDF) são lentas para executar em tabelas grandes. Uma outra alternativa para melhorar bastante a performance de funções, é utilizar funções SQLCLR, conforme demonstro os ganhos no artigo SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function.

Vídeo do Fabiano Amorim sobre o TRANSLATE

O vídeo que me inspirou a escrever esse artigo foi um do mestre Fabiano Amorim, o maior mito do SQL Server nacional (e uma das maiores referências do mundo)

E é isso aí, pessoal!
Um forte abraço e até a próxima.

Referências