SQL Server - Utilizando TRANSLATE para substituir vários REPLACE

Post Views 13,642 views

Neste artigo

Reading time 6 minutes

Hey Guys!
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]

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]

Result:

Observation: 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.

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, '$', ' ' ), 
                '@', ' ' ), '%', ' ' ), '&', ' ' ), '(', ' ' ), 
            ')', ' ' ), '+', ' ' ), '!', ' ' ), '~', ' ' ), '^', ' ' ), 
        '`', ' ' ), '´', ' ' ), '<', ' ' ), '>', ' ' ), ':', ' ' ), 
    ';', ' ' ), '?', ' ' ), 'ª', ' ' ), '{', ' ' ), '}', ' ' );

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( @Texto, '$', ' ' ),

'@', ' ' ), '%', ' ' ), '&', ' ' ), '(', ' ' ),

')', ' ' ), '+', ' ' ), '!', ' ' ), '~', ' ' ), '^', ' ' ),

'`', ' ' ), '´', ' ' ), '<', ' ' ), '>', ' ' ), ':', ' ' ),

';', ' ' ), '?', ' ' ), 'ª', ' ' ), '{', ' ' ), '}', ' ' );

Result:

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, '$', '' ), 
                '@', '' ), '%', '' ), '&', '' ), '(', '' ), 
            ')', '' ), '+', '' ), '!', '' ), '~', '' ), '^', '' ), 
        '`', '' ), '´', '' ), '<', '' ), '>', '' ), ':', '' ), 
    ';', '' ), '?', '' ), 'ª', '' ), '{', '' ), '}', '' );

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( @Texto, '$', '' ),

'@', '' ), '%', '' ), '&', '' ), '(', '' ),

')', '' ), '+', '' ), '!', '' ), '~', '' ), '^', '' ),

'`', '' ), '´', '' ), '<', '' ), '>', '' ), ':', '' ),

';', '' ), '?', '' ), 'ª', '' ), '{', '' ), '}', '' );

Result:

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)))

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)))

Result:

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:

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, '')

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, '')

Result:

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]

SELECT

[V].[AccountNumber],

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

FROM

[Purchasing].[Vendor] AS [V]

Result:

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 in this post here 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;

CREATE FUNCTION dbo.fncTranslate (

@ReplaceTarget NVARCHAR(MAX),

@from_chars NVARCHAR(MAX),

@to_chars NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

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 )

(

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 )

(

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]

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]

Result:

Important: 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)

And that's it, folks!
Um forte abraço e até a próxima.

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Utilizando TRANSLATE para substituir vários REPLACE

TRANSLATE Simples

TRANSLATE com variáveis

TRANSLATE + SELECT em tabelas

TRANSLATE nas versões anteriores ao 2017

Vídeo do Fabiano Amorim sobre o TRANSLATE

References

You may also like...

Deixe uma respostaCancelar resposta

TRANSLATE Simples

TRANSLATE com variáveis

TRANSLATE + SELECT em tabelas

TRANSLATE nas versões anteriores ao 2017

Vídeo do Fabiano Amorim sobre o TRANSLATE

References

You may also like...

SQL Server – Como identificar senhas frágeis, vazias ou iguais ao nome do usuário

SQL Server – Como utilizar o EXECUTE AS para executar comandos como outro usuário (Impersonate login e user)

SQL Server – Consultando informações da instância no Windows Registry utilizando sys.dm_server_registry e xp_instance_regread

Deixe uma respostaCancelar resposta