Find out the Binary, ASCII and Character of a Given String in SQL Server

When you are storing data inside fields like ‘address’, there are bound to be unusual characters in it which make way due to poor validation rules. A good way to look for them is to convert your string to varbinary.

Here’s the query:

DECLARE @MyAddress varchar(35)
SET @MyAddress = 'CANTB RY EA%T P.O.Box 55343'

DECLARE    @BIN AS VARBINARY(100)
SET    @BIN = convert(varbinary(100),@MyAddress)

SELECT    SUBSTRING(@BIN, Number, 1) AS Binary,
ASCII(SUBSTRING(@BIN, Number, 1)) AS ASCII,
CHAR(ASCII(SUBSTRING(@BIN, Number, 1))) AS Character
FROM    master..spt_values
WHERE    Type = 'p'
AND Number BETWEEN 1 AND DATALENGTH(@BIN)