This article was previously published under Q104829
SQL Server does not have built-in Transact-SQL command for convertingbinary data to a hexadecimal string.
The Transact-SQL CONVERT command converts binary data to character datain a one byte to one character fashion. SQL Server takes each byte ofthe source binary data, converts it to an integer value, then uses thatinteger value as the ASCII value for the destination character data.This behavior applies to the binary, varbinary, and timestamp datatypes.
For example, binary value 00001111 (0x0F in hexadecimal) is convertedinto its integer equivalent which is 15, then converted to the characterthat corresponds to ASCII value 15, which is unreadable.
The following stored procedure can be used to return a character stringwhich contains the hexadecimal representation of a binary value:
create procedure sp_hexadecimal @binvalue varbinary(255) as declare @charvalue varchar(255) declare @i int declare @length int declare @hexstring char(16) select @charvalue = '0x' select @i = 1 select @length = datalength(@binvalue) select @hexstring = "0123456789abcdef" while (@i <= @length) begin declare @tempint int declare @firstint int declare @secondint int select @tempint = convert(int, substring(@binvalue,@i,1)) select @firstint = floor(@tempint/16) select @secondint = @tempint - (@firstint*16) select @charvalue = @charvalue + substring(@hexstring, @firstint+1, 1) + substring(@hexstring, @secondint+1, 1) select @i = @i + 1 end select 'sp_hexadecimal'=@charvalue
For example, when the following command batch is executed with ISQL: