主页 / dba / 问题 / 222191
Accepted
A_V
Asked: 2018-11-10 06:29:52 +0800 CST

删除字符串结尾的不可见空字符

  • 772

由于未知原因，我的一个 VARCHAR(1000) 列中的许多字符串都以不可见字符终止。

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;

该select * from @BrokenStringTable for json auto;语句的输出如下所示：

[{"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??"}]

如何检测表中的哪些记录包含这些字符？似乎使用 charindex、where+like 和任何其他正常的解决方案都不适用于这些。

sql-server sql-server-2016

2 个回答

  1. Best Answer

    convert()我非常喜欢的原因之一cast()是convert()它更具可扩展性。例如，您可以使用样式编号将二进制值按原样转换为字符串。所以 if3F00总是有问题的字符：

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

    结果：

    borked

    因此，您可以使用以下命令找到所有违规行（这不会设置任何速度记录）：

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

  2. 在查看了 Aaron 的答案后，我找到了一种删除所有 0x000 \u0000 空字符的方法。这种技术使用一堆转换作为 varchar(max) 并且正如 Aaron 所说，不会设置任何速度记录，但它工作得很好。

    我将它包装在两个函数中，所以它最终被这样使用：

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

    将FixedColumn包含整个字符串，不包含以前破坏我的字符串正确显示的空字符。

    这是一个例子

    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'

    这是函数的代码

    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

相关问题