INF: Converting Binary Data to Hexadecimal String

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

SQL Server does not have built-in Transact-SQL command for converting binary data to a hexadecimal string.

MORE INFORMATION

The Transact-SQL CONVERT command converts binary data to character data in a one byte to one character fashion. SQL Server takes each byte of the source binary data, converts it to an integer value, then uses that integer 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 converted into its integer equivalent which is 15, then converted to the character that corresponds to ASCII value 15, which is unreadable.

The following stored procedure can be used to return a character string which 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

     declare @bin varbinary(255)
     select @bin = @@dbts
     execute sp_hexadecimal @bin
     select 'isql' = @bin
     go

     sp_hexadecimal
     ------------------------------------------------------------------
     0x01000000a60b0000

     (1 row affected)

     isql
     ------------------------------------------------------------------
     0x01000000a60b0000
     (1 row affected)