-- User Defined Function: dbo.binToHex
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[binToHex]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[binToHex]
GO



create function dbo.binToHex (@binaryValue varbinary(255))
returns varchar(255)
as
begin

declare @binToHex varchar(255),
	@curPosition int,
	@hexString char(16),
	@tempInt int,
	@firstInt int,
	@secondInt int

set @binToHex = '0x'
set @curPosition = 1
set @hexString = '0123456789abcdef'
while (@curPosition <= datalength(@binaryValue))
	begin
		select @tempInt = cast(substring(@binaryValue, @curPosition, 1) as int)
		select @firstint = floor(@tempInt/16)
		select @secondint = @tempInt - (@firstInt * 16)
		select @binToHex = @binToHex +
			substring(@hexString, @firstInt + 1, 1) +
			substring(@hexString, @secondInt + 1, 1)
		select @curPosition = @curPosition + 1
	end

return (@binToHex)

end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO