Finding char "\x00" in a varchar field
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