SQL server - how to find Hexadecimal character in a table

Question

How to search hexadecimal characters from a SQL Server table? Actually I tried like below but it is searching all zeroes in the field.

select Email,* 
from address 
where CHARINDEX(convert(varchar, Ascii(0x00)), Email) > 0

Thanks

Can you give an example of what you want to find? What would be a match, what wouldn't? — usr
– usr, Commented Aug 3, 2012 at 12:20

ChrisH · Accepted Answer · 2013-02-01 03:40:26Z

48

I had a similar problem where a hexadecimal character was crashing a query. This is what I did to find it.

select Email
from address
where Email like '%' + CHAR(0x00) +'%'

Now to stop the bad data getting in in the first place...

answered Feb 1, 2013 at 3:40

ChrisH

6215 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

This is the correct answer, at least for a similar problem I was having (illegal XML characters).