SQL Server - Como identificar e substituir "coisas estranhas" (caracteres ocultos) em strings e tabelas

Post Views 23,437 views

Neste artigo

Reading time 5 minutes

Hey guys!
Nesse artigo eu gostaria de compartilhar uma situação que costuma ocorrer de vez em quando no meu dia a dia de consultor, atendendo vários clientes e ambientes diferentes, e que na sexta-feira voltou a ocorrer, que é quando existem “coisas estranhas” em strings e tabelas (uma alusão ao seriado “Stranger Things”), que é a ocorrência de caracteres não visíveis.

Após ajudar um consultor a identificar e resolver isso, pensei em criar uma solução prática de resolver isso de forma mais rápida nas próximas vezes e também ajudar outras pessoas que possam ter esse problema no seu dia a dia, o que é costuma ocorrer especialmente em ambientes que acabaram de passar por migração ou trabalham com importação de dados de vários sistemas, API’s ou arquivos.

simulating the problem

Para demonstrar a vocês esse problema acontecendo, vou criar uma tabela bem simples, e preencher essa tabela com valores aleatórios e uma linha fixa que vou inserir. Para isso, vou utilizar uma função para gerar valores aleatórios, que é a fncRand, especialmente útil para utilizar em funções:

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

CREATE FUNCTION dbo.fncRand(

@Numero BIGINT

)

RETURNS BIGINT

BEGIN

RETURN (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * @Numero

END

Com essa função, vou criar a tabela e inserir alguns valores aleatórios:

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

IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste

CREATE TABLE #Teste (

Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

Name 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

Exemplo da tabela gerada:

Agora que criamos a tabela, vou demonstrar o que o cliente estava tentando fazer, não conseguiu e nos acionou para ajudar a entender o que estava acontecendo:

Ué.. A consulta de igualdade não retornou nada, só a utilizando o operador LIKE. Copiei e colei a string retornada pela consulta do LIKE e mesmo assim não funcionou.. O que está acontecendo ?

Identificando o problema de caracteres ocultos

Bom, provavelmente devem ter caracteres ocultos no meio da string.. Uma forma bem rápida de descobrir isso é contar a quantidade de caracteres da string e analisar quantos caracteres conseguimos visualizar:

Conforme o print acima, eu consigo visualizar 8 caracteres, mas a string possui 10, de acordo com as funções LEN e DATALENGTH, o que provavelmente indica que temos caracteres “invisíveis” na nossa coluna ou string, que podem ser os caracteres de controle da tabela ASCII (lembrando que a tabela varia de acordo com o idioma e o collation):

Para saber mais sobre a função DATALENGTH e a sua diferença para a função LEN, dê uma lida no artigo SQL Server – Como identificar as ocorrências de um caractere específico numa string ou tabela.

Para ajudar nessa identificação, criei a função abaixo que nos ajudará a identificar as linhas que possuem esses caracteres de controle:

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

CREATE FUNCTION [dbo].[fncPossui_Caractere_Oculto](

@String VARCHAR(MAX)

)

RETURNS BIT

BEGIN

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

END

Para conhecer mais sobre o PATINDEX utilizado junto com expressões regulares (RegExp), sugiro a leitura do artigo SQL Server – Como utilizar expressões regulares (RegExp) no seu banco de dados.

A sua utilização é bem simples, e retorna os registros que possuem caracteres “não visíveis”:

Identificando quais são os caracteres ocultos

Precisamos agora identificar quais são esses caracteres ocultos para avaliar se vamos tentar substituí-los ou não. Para facilitar essa tarefa, criei a função fncMostra_Caracteres_Ocultos, que vai receber a string original e retornar a posição e qual o código ASCII de cada caracter oculto na string:

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

CREATE FUNCTION [dbo].[fncMostra_Caracteres_Ocultos](

@String VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

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

Exemplo de utilização – Analisando uma string

Exemplo de utilização – Analisando registros de uma tabela

Retornando os dados sem os caracteres ocultos

Com a função fncMostra_Caracteres_Ocultos, consegui identificar quais os caracteres da minha string que estão fazendo o meu select de igualdade não retornar os dados. Vou testar se é isso mesmo:

Caso você queira retornar as informações sem os caracteres “invisíveis” de uma forma ainda mais fácil, pode utilizar a função fncRemove_Caracteres_Ocultos:

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

CREATE FUNCTION [dbo].[fncRemove_Caracteres_Ocultos](

@String VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

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

Exemplo de utilização da função fncRemove_Caracteres_Ocultos:

Caso você queira remover acentos e caracteres especiais, além dos caracteres ocultos, recomendo a leitura do artigo Como remover acentuação e caracteres especiais de uma string no SQL Server.

Observação importante: Assim como toda função UDF, essas funções podem causar uma lentidão ao serem utilizadas em grandes volumes de dados. Caso você precise utilizá-las com frequência em grandes volumes de dados, sugiro implementar funções utilizado SQLCLR, uma vez que geralmente elas entregam uma performance muito superior às funções UDF T-SQL, conforme eu já expliquei no artigo SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function.

Bom pessoal, espero que tenham gostado dessa dica que estou compartilhando com vocês e que essas funções podem ser úteis no dia a dia de vocês.
Um grande abraço e até o próximo artigo.

Paulo Teixeira disse:

August 18, 2021 às 15:00

Você esta de parabéns pelo site, muita informação bacana e com certeza se minha empresa precisar vou recomenda-lo.Muito obrigado pela ajuda.

Responder
Sandro Alemão disse:

October 10, 2019 às 17:22

Na verdade com as duas aspas na frente do hífen parou de pegar todos os caracteres…

Responder
Sandro Alemão disse:

October 10, 2019 às 16:36

Boa tarde…
Ótimo artigo Dirceu, show de bola!

Tive o mesmo problema do Alexandre e consegui resolver adicionando duas aspas ” simples na frente do hífen. Com isso o “-” parou de ser sinalizado como carácter especial.

Acredito q isso tenha ocorrido pq o hífen faz parte da sintaxe do sql quando nos referimos a listas (ex [0-9]). É por isso Dirceu?

Porém gostaria agora de adicionar o tab a lista dos permitidos, e este eu não consegui ainda. Tem como eu colocar pelo código ASCII Dirceu?

Responder
Alexandre Dantas disse:

August 23, 2019 às 09:10

Show de artigo e funções Dirceu, já estou utilizando. Obrigado.
Só fiquei com uma dúvida, no Patindex, há o caracter “-“, eu retirei das funções mas ele continua sendo retornado como o especial, pq será ?

Responder
- Dirceu Resende disse:
  
  September 18, 2019 às 23:02
  
  Me manda sua função por email que vou ver o que você alterou e te falo o que faltou, beleza?
  
  Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Como identificar e substituir “coisas estranhas” (caracteres ocultos) em strings e tabelas

simulating the problem

Identificando o problema de caracteres ocultos

Identificando quais são os caracteres ocultos

Retornando os dados sem os caracteres ocultos

You may also like...

5 Responses

Deixe uma respostaCancelar resposta

simulating the problem

Identificando o problema de caracteres ocultos

Identificando quais são os caracteres ocultos

Retornando os dados sem os caracteres ocultos

You may also like...

SQL Server – Evitando consultas em determinadas colunas com o Column Level Security (CLS)

SQL Server – Como criptografar e descriptografar senhas (com Salt) utilizando o CLR (C#)

SQL Server – Como apagar um usuário de uma instância

5 Responses

Deixe uma respostaCancelar resposta