Sql-server – Remove invisible null characters a string’s ending

sql serversql-server-2016varchar

For an unknown reason, many strings in one of my VARCHAR(1000) columns have been terminated with invisible characters.

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;

The output from the select * from @BrokenStringTable for json auto; statement looks like this :

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

How can I detect which records in my table contains these characters ? It seems using charindex, where+like and any other normal solutions just don't work with these.

Best Answer

One of the reasons I vastly prefer convert() over cast() is that convert() is much more extensible. For example, you can use a style number to convert a binary value to a string as is. So if 3F00 is always the problematic character:

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

Result:

borked

So you can find all the offending rows (this will not set any speed records) using:

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