Finding char "\x00" in a varchar field Im trying to find rows that contain certain unprintable characters and encounter some inconsistent behaviour of various string functions. The field is of type VARCHAR. Here are the queries: 

select ascii(substring(AddressTail, 17, 1)) from Address where ID = 1934484 returns 0

select charindex(char(0), substring(AddressTail, 17, 1)) from Address where ID = 1934484 returns 1 which is expected

but 

select charindex(char(0), AddressTail) from Address where ID = 1934484 returns 0 which is quite strange. 

So, how to find the rows that contain byte zero? SQL Server version is Microsoft SQL Server 2016 (SP2-CU9) (KB4515435) - 13.0.5479.0 (X64)
how to find the rows that contain byte zero?


SQL server somehow badly understands \x00...

Maybe this trick will help you slightly?

create table test (id int, txt varchar(8));

insert into test values 
(1, '123' + char(0) + '456'),                -- in the middle
(2, '123' + char(0)),                        -- at the end
(3, char(0) + '456'),                        -- at the beginning
(4, '12' + char(0) + '34' + char(0) + '56'), -- in the middle
(5, '123456');                               -- absent


select id, pos
from test
cross apply ( select number + 1 pos 
              from master..spt_values 
              where type='P'
                and number &lt; len(txt) ) numbers
where substring(txt,pos,1) = char(0)
order by id;


Output



id | pos
-: | --:
 1 |   4
 2 |   4
 3 |   1
 4 |   3
 4 |   6