¡Hola, chicos!
En este artículo me gustaría compartir una situación que suele ocurrir de vez en cuando en mi día a día como consultor, atendiendo a varios clientes y diferentes entornos, y que volvió a ocurrir el viernes, que es cuando hay “cosas raras” en cadenas y tablas (en alusión a la serie “Stranger Things”), que es la ocurrencia de personajes no visibles.

Después de ayudar a un consultor a identificar y resolver esto, pensé en crear una solución práctica para resolverlo más rápidamente la próxima vez y también ayudar a otras personas que puedan tener este problema en su vida diaria, que suele ocurrir especialmente en entornos que acaban de migrar o trabajan importando datos de varios sistemas, API o archivos.

Simulando el problema

Para demostrarle este problema, crearé una tabla muy simple y la llenaré con valores aleatorios y una línea fija que insertaré. Para hacer esto, usaré una función para generar valores aleatorios, que es fncRand, especialmente útil para su uso en funciones:

CREATE FUNCTION dbo.fncRand(
    @Numero BIGINT
)
RETURNS BIGINT
AS
BEGIN
    RETURN (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * @Numero
END
GO

Con esta función crearé la tabla e insertaré algunos valores aleatorios:

IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL
)


DECLARE 
    @Contador INT = 1, @Total INT = 100,
    @Contador2 INT = 1, @Total2 INT = 10,
    @String VARCHAR(100)

WHILE(@Contador <= @Total)
BEGIN
    

    SET @Contador2 = 1
    SET @String = ''


    WHILE(@Contador2 <= @Total2)
    BEGIN

        IF (@Contador2 <= 8)
            SET @String += CHAR(65 + dbo.fncRand(25))
        ELSE
            SET @String += CHAR(dbo.fncRand(255))


        SET @Contador2 += 1


    END


    INSERT INTO #Teste
    VALUES(@String)

    SET @Contador += 1

END

Ejemplo de la tabla generada:

Ahora que hemos creado la tabla, demostraré lo que el cliente estaba intentando hacer, falló y se comunicó con nosotros para ayudarlo a comprender lo que estaba sucediendo:

Eh... La consulta de igualdad no arrojó nada, solo usó el operador LIKE. Copié y pegué la cadena devuelta por la consulta LIKE y todavía no funcionó. ¿Lo que está sucediendo?

Identificar el problema de los personajes ocultos

Bueno, probablemente debe haber caracteres ocultos en el medio de la cadena. Una forma muy rápida de saberlo es contar el número de caracteres de la cadena y analizar cuántos caracteres podemos ver:

Según el print de arriba, puedo ver 8 caracteres, pero la cadena tiene 10, según las funciones LEN y DATALENGTH, lo que probablemente indica que tenemos caracteres “invisibles” en nuestra columna o cadena, que podrían ser los caracteres de control de la tabla ASCII (recordando que la tabla varía según el idioma y el intercalado):

Para obtener más información sobre la función DATALENGTH y su diferencia con la función LEN, lea el artículo SQL Server: cómo identificar apariciones de un carácter específico en una cadena o tabla.

Para ayudar con esta identificación, creé la siguiente función que nos ayudará a identificar las líneas que tienen estos caracteres de control:

CREATE FUNCTION [dbo].[fncPossui_Caractere_Oculto](
    @String VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
    RETURN (CASE WHEN PATINDEX('%[^ !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ\^_`abcdefghijklmnopqrstuvwxyz|{}~€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ[[]%', REPLACE(@String, ']', '')) > 0 THEN 1 ELSE 0 END)
END

Para obtener más información sobre PATINDEX utilizado junto con expresiones regulares (RegExp), sugiero leer el artículo. SQL Server: cómo utilizar expresiones regulares (RegExp) en su base de datos.

Su uso es muy sencillo y devuelve registros que tienen caracteres “no visibles”:

Identificar qué personajes están ocultos

Ahora nos falta identificar cuáles son estos personajes ocultos para valorar si vamos a intentar sustituirlos o no. Para facilitar esta tarea, creé la función fncMostra_Caracteres_Ocultos, que recibirá la cadena original y devolverá la posición y el código ASCII de cada carácter oculto en la cadena:

CREATE FUNCTION [dbo].[fncMostra_Caracteres_Ocultos](
    @String VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE 
        @Result VARCHAR(MAX) = '', 
        @Contador INT = 1,
        @Total INT,
        @AdicionarBarra BIT = 0
    
    
    SET @Total = LEN(@String)

    WHILE(@Contador <= @Total)
    BEGIN
        
        IF (PATINDEX('%[^ !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ\^_`abcdefghijklmnopqrstuvwxyz|{}~€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ[[]%', SUBSTRING(REPLACE(@String, ']', ''), @Contador, 1)) > 0)
        BEGIN
            SET @Result += (CASE WHEN @AdicionarBarra = 1 THEN ' | ' ELSE '' END) + 'Pos ' + CAST(@Contador AS VARCHAR(100)) + ': CHAR(' + CAST(ASCII(SUBSTRING(@String, @Contador, 1)) AS VARCHAR(5)) + ')'
            SET @AdicionarBarra = 1
        END

        SET @Contador += 1

    END
    
    RETURN @Result

END
GO

Ejemplo de uso: análisis de una cadena

Ejemplo de uso: análisis de registros de una tabla

Devolver datos sin caracteres ocultos

Con la función fncMostra_Caracteres_Ocultos pude identificar qué caracteres de mi cadena están provocando que mi selección de igualdad no devuelva datos. Probaré si esto es cierto:

Si quieres devolver la información sin los caracteres “invisibles” de una forma aún más sencilla, puedes utilizar la función fncRemove_Caracteres_Hidden:

CREATE FUNCTION [dbo].[fncRemove_Caracteres_Ocultos](
    @String VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    
    DECLARE 
        @Result VARCHAR(MAX), 
        @StartingIndex INT = 0
    
    
    WHILE (1 = 1)
    BEGIN 
        
        SET @StartingIndex = PATINDEX('%[^ !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ\^_`abcdefghijklmnopqrstuvwxyz|{}~€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ[[]%', REPLACE(@String, ']', ''))
        
        IF (@StartingIndex <> 0)
            SET @String = REPLACE(@String,SUBSTRING(@String, @StartingIndex,1),'') 
        ELSE
            BREAK

    END	
    
    SET @Result = REPLACE(@String,'|','')
    
    RETURN @Result

END
GO

Ejemplo de uso de la función fncRemove_Caracteres_Hidden:

Si quieres eliminar acentos y caracteres especiales, además de caracteres ocultos, te recomiendo leer el artículo. Cómo eliminar acentuaciones y caracteres especiales de una cadena en SQL Server.

Nota importante: Como ocurre con todas las funciones UDF, estas funciones pueden provocar ralentizaciones cuando se utilizan en grandes volúmenes de datos. Si necesita utilizarlas frecuentemente en grandes volúmenes de datos, le sugiero implementar funciones usando SQLCLR, ya que generalmente ofrecen un rendimiento mucho mejor que las funciones UDF T-SQL, como ya expliqué en el artículo. SQL Server: comparación de rendimiento entre la función escalar y la función escalar CLR.

Bueno chicos, espero que les haya gustado este tip que les comparto y que estas funciones les puedan ser útiles en su vida diaria.
Un fuerte abrazo y nos vemos en el próximo artículo.