faster dbo.ufn_vbintohexstr - varbinary to hex

,

Here's an alternative to Clinton Herring's ufn_vbintohexstr which should be much faster with large varbinary values.

First, in his original version, the inner-loop CASE statements can be replaced with this:

select @value = @value +

CHAR(@vbin/16+48+(@vbin+96)/256*7)

+CHAR(@vbin&15+48+((@vbin&15)+6)/16*7)

How does it work? By adding 6 to a hex-digit in (@vbin&15), you have a value from 16 to 21. If you integer-divide by 16, it's always 1. multiply by 7, and you have the amount needed to add to turn 0x0A to 'A', given that 0x30 (48) has already been added. (Okay, I know it's confusing)

Anyway, the neat thing about this technique is that it can be done for 4 bytes in parallel. Below is the entire arith-8 version, producing 8-hex-digits at a time.

create function dbo.ufn_vbintohexstr (
 @vbin_in varbinary(256)
)
returns varchar(514)
as
Begin
   declare @x bigint
   declare @len int
   declare @loop int
   declare @value varbinary(514)
   set @value = 0x
   set @loop = 1
   set @len = datalength(@vbin_in)
   if (@len & 1) <> 0
      set @vbin_in = 0x00 + @vbin_in
   if (@len & 3) < 3
      set @vbin_in = 0x0000 + @vbin_in
   while @loop <= @len
      begin
         set @x = CAST(substring(@vbin_in,@loop,4)AS BIGINT)
         set @x =65536*
          ( (@x&0xF0000000)*4096
           +(@x&0x0F000000)*256
           +(@x&0x00F00000)*16
           +(@x&0x000F0000) )
           +(@x&0xF000)*4096
           +(@x&0x0F00)*256
           +(@x&0x00F0)*16
           +(@x&0x000F)
         set @x = (@x | 0x3030303030303030)+
             ((@x+0x0606060606060606)/16
                & 0x0101010101010101)*7
         select @value  = @value + CAST(@x AS BINARY(8))
         set @loop = @loop + 4
      end
   return '0x'+ right(CAST(@value AS VARCHAR(514)), @len*2)
end
GO
select dbo.ufn_vbintohexstr(0x123456789abcdef1234)
-- outputs: 0x0123456789ABCDEF1234
GO

Rate

Share

Share

Rate