Converting Binary data to a Hex Character String
Occassionally you might want to convert a binary number into some printable string. This would allow you display some meaning fully numbers hex characters, instead of just some unreadable garbage. This script takes a binary field, of basically any length, and converts it, character by character, to a character string. The character string will contain a string of hex characters that is equivilant to the binary number.
-- Write by: Gregory A. Larsen
declare @binary_field varbinary(85)
declare @c int
declare @hexnum char(100)
set @binary_field = 0x12323DFE345987AD980089A12312312312312345345344567CDAFE
set @hexnum = '0x'
while len(@binary_field) > 0
begin
set @c=cast(substring(@binary_field,1,1) as int)
set @binary_field=substring(@binary_field,2,len(@binary_field))
set @hexnum = rtrim(@hexnum) +
case
when (@c)/power(16,1)%16 = 0 then '0'
when (@c)/power(16,1)%16= 1 then '1'
when (@c)/power(16,1)%16= 2 then '2'
when (@c)/power(16,1)%16 = 3 then '3'
when (@c)/power(16,1)%16 = 4 then '4'
when (@c)/power(16,1)%16 = 5 then '5'
when (@c)/power(16,1)%16 = 6 then '6'
when (@c)/power(16,1)%16 = 7 then '7'
when (@c)/power(16,1)%16 = 8 then '8'
when (@c)/power(16,1)%16 = 9 then '9'
when (@c)/power(16,1)%16 = 10 then 'A'
when (@c)/power(16,1)%16 = 11 then 'D'
when (@c)/power(16,1)%16 = 12 then 'C'
when (@c)/power(16,1)%16 = 13 then 'D'
when (@c)/power(16,1)%16 = 14 then 'E'
when (@c)/power(16,1)%16 = 15 then 'F'
end +
case
when (@c)/power(16,0)%16 = 0 then '0'
when (@c)/power(16,0)%16= 1 then '1'
when (@c)/power(16,0)%16= 2 then '2'
when (@c)/power(16,0)%16 = 3 then '3'
when (@c)/power(16,0)%16 = 4 then '4'
when (@c)/power(16,0)%16 = 5 then '5'
when (@c)/power(16,0)%16 = 6 then '6'
when (@c)/power(16,0)%16 = 7 then '7'
when (@c)/power(16,0)%16 = 8 then '8'
when (@c)/power(16,0)%16 = 9 then '9'
when (@c)/power(16,0)%16 = 10 then 'A'
when (@c)/power(16,0)%16 = 11 then 'D'
when (@c)/power(16,0)%16 = 12 then 'C'
when (@c)/power(16,0)%16 = 13 then 'D'
when (@c)/power(16,0)%16 = 14 then 'E'
when (@c)/power(16,0)%16 = 15 then 'F'
end
end
print 'The binary number in a character variable is : ' + @hexnum 
