ufn_vbintohexstr

Alter function dbo.ufn_vbintohexstr (
      @vbin_in varbinary(256)
      )
returns varchar(514)
as

/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/--Name        : dbo.ufn_vbintohexstr        for SQL 2K
--
--Description : Converts a binary to the equivalent hex string, i.e., 
--              11111111 converts to 0xFF
--
--Parameters  : @vbin_in - the varbinary value to convert; returns the hex
--                         string equivalent.
--
--Comments    : A binary byte such as 11111111 requires 2 char characters and
--              the leading '0x' in order to display correctly the hex string
--              so a binary(16) would require a char(34) for correct display
--              or storage.
--
--Note        : This method shifts nybbles to get one nybble per individual
--              byte and then converts the values to ascii equivalents by 
--              first adding 48 (0x30) to each elongated byte and then uses
--              a step function to add the necessary 7 if A-F. A final convert
--              to varchar returns the hex string value. Fast & efficient.
--
--Method from : Aaron West
--Author      : Clinton Herring
--Date        : 05/12/2005
--
--History     : 
--
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/
Begin
   declare @big bigint
   declare @len int
   declare @loop int
   declare @value varbinary(514)
   set @value = 0x
   set @loop = 1
   set @len = Datalength(@vbin_in) 
   while @loop <= @len
      begin
         set @big = CAST(CAST(Substring(@vbin_in,@loop,4)as binary(4)) as bigint)
         set @big = 
            (@big&0xF0000000)*0x10000000
           |(@big&0x0F000000)*0x01000000
           |(@big&0x00F00000)*0x00100000
           |(@big&0x000F0000)*0x00010000
           |(@big&0x0000F000)*0x00001000
           |(@big&0x00000F00)*0x00000100
           |(@big&0x000000F0)*0x00000010
           |(@big&0x0000000F) 
         select @value = @value + CAST(@big | 0x3030303030303030 + ((@big+0x0606060606060606)/16&0x0101010101010101)*7 as binary(8))
         set @loop = @loop + 4
      end
   return '0x'+ CAST(LEFT(@value, @len*2) as varchar(514))
end