Converting binary to Hexadecimal String in SQL Server

Here is a stored procedure that can cast binary values to hexadecimal. It becomes very handy when working with binary fields. Unfortunately, SQL Server does not have a build-in cast for converting binary to hexadecimal.

 

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

 

Source:

http://support.microsoft.com/kb/104829