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.


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:

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



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';
  • Thanks I managed to detect them, off to clean them – A_V Nov 9 '18 at 15:25
  • I know it’s not necessarily related, by SQL Variants have a similar effect (except they break Windows). I wonder if you can use the same logic to make the invisible character show up – clifton_h Nov 10 '18 at 3:16

After looking at Aaron's answer, I found a way to remove all of the 0x000 \u0000 null characters. This technique uses a bunch of converts as varchar(max) and as Aaron said won't set any speed records but it worked fine.

I wrapped it inside two functions so it ends up being used like this :

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

The FixedColumn will contain the entire string without the null characters which previously broke the correct display of my string.

Here is an example

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'

Here is the code for the functions

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

CREATE FUNCTION [dbo].ContainsNullCharacters
    @String NVARCHAR(MAX)
    RETURN case when CHARINDEX(0x0000,convert(varbinary(max),@String))>0 then 1 else 0 end
  • 1
    Simply looking for and deleting 0x0000 may result in data corruption. I think the search needs to be a little more sophisticated. – Andriy M Nov 9 '18 at 16:34
  • Arggh. You're right i.imgur.com/2oIq2mm.png Now can I fix this – A_V Nov 9 '18 at 16:46
  • A 0x0000 sequence is a null character only if the position it's found at is an odd number. So the check function will be easy to fix. Edit: actually no, it probably won't be, I was too optimistic there. The removal function, not so much. – Andriy M Nov 9 '18 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.