SQL Server - Using TRANSLATE to replace several REPLACE

Views: 10.109 views

In this article

Reading Time: 6 minutes

Hello everybody!
In this quick post here, I'd like to share with you some use cases of the TRANSLATE function, available since SQL Server 2017, which can help replace multiple REPLACE commands with just one TRANSLATE command, greatly reducing code size and complexity.

Spoiler: Do you use a version before 2017? Calm down. There is a way for everything 🙂

To understand how the TRANSLATE command works and its differences to REPLACE, I've prepared some cool examples for you.

TRANSLATE Simple

In this first example, we will demonstrate how TRANSLATE works in practice and how to have the same behavior using 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: In the example above, I used TRANSLATE to remove special characters from a string. You can also do this using other options, like the ones I've shared in the articles below:

TRANSLATE with variables

In this other example, I want to show that it is possible to use TRANSLATE with variables. I will also show a difference in the behavior of TRANSLATE compared to 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 [email protected]@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:

In the example above, it was very clear how TRANSLATE facilitates the writing and reading of code, avoiding typing errors, reducing complexity and facilitating maintenance and reading. But the 2 had the same result. And I wish it didn't have this spacing between characters, as in the print above.

Let's try to remove this.

At REPLACE it was pretty easy. Just change the space character ” ” by an empty string “”:

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 [email protected]@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:

Now I'm going to try using 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 [email protected]@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.

That is, I can't do the same thing with TRANSLATE in this case, because the length of the string to be found and the string to be replaced must be the same length. As much as I'm using REPLICATE to force the length to be equal, as the string is empty (“”), the number of characters doesn't increase.

This example then justifies the use of REPLACE in certain scenarios when you want to replace a character with an empty string, right?

Calm down.. We can try a "workaround" to solve this, merging a TRANSLATE with a single REPLACE, where I'll replace all the special characters by the null character (character 0) using TRANSLATE and then I'll replace all nulls by an empty string with a single 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 [email protected]@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 on tables

The TRANSLATE command also works normally if applied to datasets such as tables and 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:

In the example above, I used TRANSLATE to remove numbers from a string. You can also do this using other options, like the ones I've shared in the articles below:

TRANSLATE in versions prior to 2017

As I mention at the beginning of the post, the TRANSLATE function is only available starting with SQL Server 2017, so if you need to use it in earlier versions, you'll have to make do with lots of nested REPLACE commands, right? NOT!!

I will share with you this function I found in this post here from StackOverflow and I made a few modifications so I don't need to create external tables.

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;

Usage is the same as the original function:

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: In general, user functions (UDF) can cause big performance problems, especially if used in the WHERE/JOIN clause and in very large tables. To learn more about this, read my post. SQL Server - Using Calculated Columns (or Computed Columns) for Performance Tuning.

Another tip: In general, user functions (UDF) are slow to run on large tables. Another alternative to greatly improve the performance of functions is to use SQLCLR functions, as I demonstrate the gains in the article SQL Server - Performance Comparison between Scalar Function and CLR Scalar Function.

Fabiano Amorim's video about TRANSLATE

The video that inspired me to write this article was one by the master Fabiano Amorim, the biggest national SQL Server myth (and one of the biggest references in the world)

And that's it folks!
A big hug and see you next time.

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server – Using TRANSLATE to replace multiple REPLACE

TRANSLATE Simple

TRANSLATE with variables

TRANSLATE + SELECT on tables

TRANSLATE in versions prior to 2017

Fabiano Amorim's video about TRANSLATE

References

You may also like ...

Leave a Comment Cancel reply

TRANSLATE Simple

TRANSLATE with variables

TRANSLATE + SELECT on tables

TRANSLATE in versions prior to 2017

Fabiano Amorim's video about TRANSLATE

References

You may also like ...

Meet Power CLR: SQL Server Integration with APIs, Files, Webservices, FTP and more!

SQL Server - Error using OPENROWSET: Access to the remote server is denied because no login-mapping exists

How was the 7th PASS Local Group SQL Server ES meeting?

Leave a Comment Cancel reply