¡Hola, chicos!
En esta publicación rápida, me gustaría compartir con ustedes algunos casos de uso de la función TRADUCIR, disponible desde SQL Server 2017, que puede ayudar a reemplazar varios comandos REEMPLAZAR con un solo comando TRADUCIR, reduciendo en gran medida la complejidad y el tamaño de los códigos.

Spoiler: ¿Estás usando una versión anterior a 2017? Cálmate.. Hay una manera para todo 🙂

Para comprender cómo funciona el comando TRADUCIR y sus diferencias con REEMPLAZAR, preparé algunos ejemplos interesantes para usted.

TRADUCIR Sencillo

En este primer ejemplo, demostraremos cómo funciona TRANSLATE en la práctica y cómo tener el mismo comportamiento usando 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:

Observación: En el ejemplo anterior, utilicé TRANSLATE para eliminar caracteres especiales de una cadena. También puedes hacer esto usando otras opciones, como las que compartí en los artículos siguientes:

TRADUCIR con variables

En este otro ejemplo, quiero mostrar que es posible usar TRANSLATE con variables. También mostraré una diferencia en el comportamiento de TRADUCIR en comparación con REEMPLAZAR.

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:

En el ejemplo anterior quedó muy claro cómo TRANSLATE facilita la escritura y lectura de código, evitando errores tipográficos, reduciendo la complejidad y facilitando el mantenimiento y la lectura. Pero ambos presentaron el mismo resultado. Y desearía que no hubiera este espacio entre caracteres, como en la impresión de arriba.

Intentemos eliminar esto.

En REPLACE fue muy fácil. Todo lo que tenías que hacer era reemplazar el carácter de espacio “” con una cadena vacía “”:

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:

Ahora voy a intentar usar TRADUCIR:

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:

Mensaje 9828, Nivel 16, Estado 1, Línea 7
El segundo y tercer argumento de la función incorporada TRADUCIR deben contener el mismo número de caracteres.

En otras palabras, no puedo hacer lo mismo con TRANSLATE en este caso, porque el tamaño de la cadena a ubicar y la cadena a reemplazar deben ser del mismo tamaño. Aunque estoy usando REPLICATE para forzar que el tamaño sea el mismo, como la cadena está vacía (“”), la cantidad de caracteres no aumenta.

Este ejemplo justifica el uso de REPLACE en ciertos escenarios cuando desea reemplazar un carácter con una cadena vacía, ¿correcto?

Cálmate... Podemos probar una “solución alternativa” para resolver esto, fusionando un TRADUCIR con un solo REEMPLAZAR, donde reemplazaré todos los caracteres especiales con el carácter nulo (carácter 0) usando TRADUCIR y luego reemplazaré todos los valores nulos con una cadena vacía con un solo REEMPLAZO:

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:

TRADUCIR + SELECCIONAR en tablas

El comando TRADUCIR también funciona normalmente cuando se aplica a conjuntos de datos, como tablas y vistas:

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

Resultado:

En el ejemplo anterior, utilicé TRANSLATE para eliminar números de una cadena. También puedes hacer esto usando otras opciones, como las que compartí en los artículos siguientes:

TRADUCIR en versiones anteriores a 2017

Como comenté al inicio del post, la función TRADUCIR solo está disponible a partir de SQL Server 2017 en adelante, es decir, si necesitas usarla en versiones anteriores, tendrás que conformarte con varios comandos REPLACE anidados, ¿no? ¡¡NO!!

Compartiré contigo esta función que encontré. en esta publicación aquí de StackOverflow e hice algunas pequeñas modificaciones para no necesitar crear tablas 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;

El uso es el mismo que el de la función 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:

Importante: En general, las funciones de usuario (UDF) pueden causar importantes problemas de rendimiento, especialmente si se usan en la cláusula WHERE/JOIN y en tablas muy grandes. Para obtener más información sobre esto, lee mi publicación. SQL Server: uso de columnas calculadas (o columnas calculadas) para ajustar el rendimiento.

Otro consejo: En términos generales, las funciones de usuario (UDF) se ejecutan lentamente en tablas grandes. Otra alternativa para mejorar mucho el rendimiento de las funciones es utilizar funciones SQLCLR, como demuestro las ganancias en el artículo. SQL Server: comparación de rendimiento entre la función escalar y la función escalar CLR.

Vídeo de Fabiano Amorim sobre TRADUCIR

El vídeo que me inspiró a escribir este artículo fue uno del maestro. Fabiano Amorim, el mayor mito nacional de SQL Server (y uno de los mayores referentes del mundo)

¡Y eso es todo, amigos!
Un fuerte abrazo y hasta la próxima.

Referencias