You are currently offline, waiting for your internet to reconnect

INFO: Converting Binary Data to Hexadecimal String

This article was previously published under Q104829
SUMMARY
SQL Server does not have built-in Transact-SQL command for convertingbinary data to a hexadecimal string.
MORE INFORMATION
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:
     declare @bin varbinary(255)     select @bin = @@dbts     execute sp_hexadecimal @bin     select 'isql' = @bin     go				

it returns output similar to the following:
     sp_hexadecimal     ------------------------------------------------------------------     0x01000000a60b0000     (1 row affected)     isql     ------------------------------------------------------------------     0x01000000a60b0000     (1 row affected)				
Windows NT
Properties

Article ID: 104829 - Last Review: 02/22/2005 22:34:48 - Revision: 3.2

  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 6.5 Service Pack 1
  • Microsoft SQL Server 6.5 Service Pack 2
  • Microsoft SQL Server 6.5 Service Pack 3
  • Microsoft SQL Server 6.5 Service Pack 4
  • Microsoft SQL Server 6.5 Service Pack 5a
  • Microsoft SQL Server 6.5 Service Pack 5a
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 7.0 Service Pack 1
  • kbinfo kbother KB104829
Feedback