SQL Server - Utilizando TRANSLATE para substituir vários REPLACE

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:

DECLARE @Texto VARCHAR(100) = 'Testando essa string com c4r4ct3r3$ 3$p3c141$'

-- O QUE EU QUERO?
-- SUBSTITUIR "4" por "a" | "3" por "e" | "$" por "s" | "1" por "i"

-- TRANSLATE
SELECT TRANSLATE(@Texto, '43$1', 'aesi') AS [TRANSLATE]

-- REPLACE
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@Texto, '4', 'a'), '3', 'e'), '$', 's'), '1', 'i') AS [REPLACE]

Resultado:

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.

DECLARE 
    @Texto VARCHAR(100) = 'Testando essa string com ca$r@a@c%t&e(r)e+s e!s~p^e`c´i<a>i:s;?ª{}',
    @Encontrar VARCHAR(100) = '$@%&()+!~^`´<>:;?ª{}',
    @Substituir VARCHAR(100) = ''

SET @Substituir = REPLICATE(' ', LEN(@Encontrar))


-- TRANSLATE
SELECT TRANSLATE(@Texto, @Encontrar, @Substituir)

-- REPLACE
SELECT
    REPLACE( REPLACE( REPLACE( REPLACE( 
        REPLACE( REPLACE( REPLACE( REPLACE( 
            REPLACE( REPLACE( REPLACE( REPLACE( 
                REPLACE( REPLACE( REPLACE( REPLACE( 
                    REPLACE( REPLACE( REPLACE( REPLACE( @Texto, '$', ' ' ), 
                '@', ' ' ), '%', ' ' ), '&', ' ' ), '(', ' ' ), 
            ')', ' ' ), '+', ' ' ), '!', ' ' ), '~', ' ' ), '^', ' ' ), 
        '`', ' ' ), '´', ' ' ), '<', ' ' ), '>', ' ' ), ':', ' ' ), 
    ';', ' ' ), '?', ' ' ), 'ª', ' ' ), '{', ' ' ), '}', ' ' );

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 “”:

DECLARE 
    @Texto VARCHAR(100) = 'Testando essa string com ca$r@a@c%t&e(r)e+s e!s~p^e`c´i<a>i:s;?ª{}',
    @Encontrar VARCHAR(100) = '$@%&()+!~^`´<>:;?ª{}'

-- REPLACE
SELECT
    REPLACE( REPLACE( REPLACE( REPLACE( 
        REPLACE( REPLACE( REPLACE( REPLACE( 
            REPLACE( REPLACE( REPLACE( REPLACE( 
                REPLACE( REPLACE( REPLACE( REPLACE( 
                    REPLACE( REPLACE( REPLACE( REPLACE( @Texto, '$', '' ), 
                '@', '' ), '%', '' ), '&', '' ), '(', '' ), 
            ')', '' ), '+', '' ), '!', '' ), '~', '' ), '^', '' ), 
        '`', '' ), '´', '' ), '<', '' ), '>', '' ), ':', '' ), 
    ';', '' ), '?', '' ), 'ª', '' ), '{', '' ), '}', '' );

Resultado:

Agora vou tentar utilizando o TRANSLATE:

DECLARE 
    @Texto VARCHAR(100) = 'Testando essa string com ca$r@a@c%t&e(r)e+s e!s~p^e`c´i<a>i:s;?ª{}',
    @Encontrar VARCHAR(100) = '$@%&()+!~^`´<>:;?ª{}',
    @Substituir VARCHAR(100) = ''

-- TRANSLATE
SELECT TRANSLATE(@Texto, @Encontrar, REPLICATE(@Substituir, LEN(@Encontrar)))

Resultado:

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:

DECLARE 
    @Texto VARCHAR(100) = 'Testando essa string com ca$r@a@c%t&e(r)e+s e!s~p^e`c´i<a>i:s;?ª{}',
    @Encontrar VARCHAR(100) = '$@%&()+!~^`´<>:;?ª{}',
    @Substituir VARCHAR(100) = CHAR(0)

-- TRANSLATE + REPLACE
SELECT 
    REPLACE(
        TRANSLATE(@Texto, @Encontrar, REPLICATE(@Substituir, LEN(@Encontrar))), 
    @Substituir, '')

Resultado:

TRANSLATE + SELECT em tabelas

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

SELECT
    [V].[AccountNumber],
    REPLACE( TRANSLATE( [V].[AccountNumber], '0123456789', REPLICATE( CHAR( 0 ), 10)), CHAR( 0 ), '' ) AS AccountNumberSemNumeros
FROM
    [Purchasing].[Vendor] AS [V]

Resultado:

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.

CREATE FUNCTION dbo.fncTranslate (
    @ReplaceTarget NVARCHAR(MAX),
    @from_chars    NVARCHAR(MAX),
    @to_chars      NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    
    DECLARE @numbers TABLE ( id INT );
    INSERT INTO @numbers VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

    DECLARE @steps INT = LEN( '_' + @from_chars + '_' ) - 2;

    WITH dictionary ( id, string_from, string_interim, string_to )
    AS
    (
        SELECT
            id,
            string_from,
            N'<' + string_from + N'>',
            string_to
        FROM
        (
            SELECT
                id,
                ROW_NUMBER() OVER ( PARTITION BY string_from ORDER BY id ) AS occurence,
                string_from,
                string_to
            FROM
                @numbers AS A
                CROSS APPLY
            (
                SELECT
                    CAST(SUBSTRING( @from_chars, A.id, 1 ) AS NVARCHAR(5)) AS string_from,
                    CAST(SUBSTRING( @to_chars, A.id, 1 ) AS NVARCHAR(5))   AS string_to
            ) chars
            WHERE
                A.id > 0
                AND A.id <= @steps
        ) sorted_dictionary
        WHERE
            occurence = 1
    ),
    mapping_sequence ( id, string_from, string_to )
    AS
    (
        SELECT 1, N'<', N'<<>' WHERE @from_chars LIKE N'%<%'
        UNION ALL
        SELECT 2, N'>', N'<>>' WHERE @from_chars LIKE N'%>%'
        UNION ALL
        SELECT 3, N'<<<>>', N'<<>' WHERE @from_chars LIKE N'%<%' AND @from_chars LIKE N'%>%'
        UNION ALL
        SELECT 3 + id, string_from, string_interim FROM dictionary WHERE string_from NOT IN ( N'<', N'>' )
        UNION ALL
        SELECT 3 + @steps + id, string_interim, string_to FROM dictionary
    )
    SELECT
        @ReplaceTarget = REPLACE( @ReplaceTarget, string_from, string_to )
    FROM
        mapping_sequence
    ORDER BY
        id;


    RETURN @ReplaceTarget;

END;

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

DECLARE @Texto VARCHAR(100) = 'Testando essa string com c4r4ct3r3$ 3$p3c141$'

-- O QUE EU QUERO?
-- SUBSTITUIR "4" por "a" | "3" por "e" | "$" por "s" | "1" por "i"

-- TRANSLATE
SELECT TRANSLATE(@Texto, '43$1', 'aesi') AS [TRANSLATE]

-- TRANSLATE UDF - SQL ANTIGO
SELECT dbo.fncTranslate(@Texto, '43$1', 'aesi') AS [TRANSLATE_UDF]

Resultado:

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

• TRANSLATE (Transact-SQL)
• TRANSLATE function in SQL SERVER
• Null character ASCII CODE 0