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

You rated this post out of 5. Change rating

Rate

You rated this post out of 5. Change rating