Início / dba / Perguntas / 222191
Accepted
A_V
Asked: 2018-11-10 06:29:52 +0800 CST

Remover caracteres nulos invisíveis do final de uma string

  • 772

Por um motivo desconhecido, muitas strings em uma das minhas colunas VARCHAR(1000) foram encerradas com caracteres invisíveis.

declare @BrokenString varbinary(max)=0x6D0079002000620075006700670065006400200073007400720069006E00670000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003F003F00;
select cast(@BrokenString as nvarchar(max)) -- returns 'my bugged string'
select cast(@BrokenString as nvarchar(max)) + ' is bugged' -- still returns 'my bugged string' !

declare @BrokenStringTable table (Brokey nvarchar(max));
insert into @BrokenStringTable
select cast(@BrokenString as nvarchar(max));
select * from @BrokenStringTable for json auto;

A saída da select * from @BrokenStringTable for json auto;instrução se parece com isso:

[{"Brokey":"my bugged string\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000??"}]

Como posso detectar quais registros na minha tabela contém esses caracteres? Parece que usar charindex, where+like e quaisquer outras soluções normais simplesmente não funcionam com elas.

sql-server sql-server-2016

2 respostas

  1. Best Answer

    Uma das razões pelas quais eu prefiro muito convert()mais cast()é que convert()é muito mais extensível. Por exemplo, você pode usar um número de estilo para converter um valor binário em uma string como está . Então, se 3F00é sempre o personagem problemático:

    SELECT CASE 
  WHEN CONVERT(nvarchar(max), @BrokenString, 1) LIKE N'%3F00' 
  THEN 'borked' END;

    Resultado:

    borked

    Assim, você pode encontrar todas as linhas ofensivas (isso não definirá nenhum recorde de velocidade) usando:

    SELECT ... FROM dbo.table 
WHERE CONVERT(nvarchar(max), column) LIKE N'%3F00';
    • 5

  2. Depois de analisar a resposta de Aaron, encontrei uma maneira de remover todos os caracteres nulos 0x000 \u0000. Esta técnica usa um monte de conversões como varchar(max) e, como Aaron disse, não definirá nenhum recorde de velocidade, mas funcionou bem.

    Eu o envolvi em duas funções para que ele seja usado assim:

    select BrokenStringColumn
,dbo.RemoveNullCharacters(BrokenStringColumn) 'FixedColumn'
from BrokenTable
where dbo.ContainsNullCharacters(BrokenStringColumn)=1

    O FixedColumnconterá a string inteira sem os caracteres nulos que anteriormente quebravam a exibição correta da minha string.

    Aqui está um exemplo

    declare @BadString nvarchar(max)= convert(nvarchar(max),0x6d0000007900) 
,@GoodString nvarchar(max)= convert(nvarchar(max),0x6d007900)
select @BadString 'badstring'                 -- result : 'm'
,@GoodString 'goodstring'                     -- result : 'my'
,@BadString+ 'test' 'failed_concat'           -- result : 'm'
,dbo.ContainsNullCharacters(@BadString) 'cb'  -- result : 1
,dbo.ContainsNullCharacters(@GoodString) 'cg' -- result : 0
,dbo.RemoveNullCharacters(@BadString)+' test' -- result : 'my test'

    Aqui está o código para as funções

    CREATE FUNCTION [dbo].RemoveNullCharacters
(
    @String NVARCHAR(MAX)
)
RETURNS nvarchar(max)
AS
BEGIN
    RETURN convert(nvarchar(max),convert(varbinary(max),replace(convert(varbinary(max),@String),0x0000,0x)))
END
GO


CREATE FUNCTION [dbo].ContainsNullCharacters
(
    @String NVARCHAR(MAX)
)
RETURNS bit
AS
BEGIN
    RETURN case when CHARINDEX(0x0000,convert(varbinary(max),@String))>0 then 1 else 0 end
END
    • 1

relate perguntas