SQL server - how to find Hexadecimal character in a table

_{Friday, August 3, 2012 11:50 AM}

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

_{Friday, August 3, 2012 12:10 PM ✅Answered}

One method:

SELECT
    EMail
    ,CHARINDEX(0x00, CAST(EMail AS varbinary(30))) Position
FROM dbo.address
WHERE
    CHARINDEX(0x00, CAST(EMail AS varbinary(30))) > 0;

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

_{Friday, August 3, 2012 12:16 PM}

also you can try to find SELECT Ascii(0x0C) in the table OR try to find LIKE '%' + Ascii(0x0C) ) + '%'.

regards

joon