HexToInt, vbintoHexStr - fun with CAST

[Aaron W. West]

Fun with CAST! (Optimized SQLServerCentra l script posts)

I found some interesting "tricks" to convert binary to hexadecimal and back,
which allow doing 4 or 8 at a time.

Test code first:

-- These two have the same output, other than the width:
select dbo.ufn_vbintoh exstr(0x1234567 89abcdef1234)
select 0x123456789abcd ef1234

create function dbo.ufn_vbintoh exstr (
@vbin_in varbinary(256)
)
returns varchar(514)
as
Begin
declare @x bigint
declare @y bigint
declare @len int
declare @loop int
declare @value varbinary(514)
set @value = 0x
set @loop = 1
set @len = datalength(@vbi n_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 = CAST(CAST(0x00 + CAST(CAST(CAST( CAST(@x & 0x0F0F0F0F AS
BINARY(4))
AS CHAR(4))AS NCHAR(4))AS BINARY(8))AS BINARY(8))AS BIGINT)
+ CAST(CAST(CAST( CAST(CAST((@x/16) & 0x0F0F0F0F AS
BINARY(4))
AS CHAR(4))AS NCHAR(4))AS BINARY(8))AS BIGINT)
set @x = @x + 0x3030303030303 030
+( (@x+0x060606060 6060606)/16
&0x010101010101 0101 )*7
select @value = @value + CAST(@x AS BINARY(8))
set @loop = @loop + 4
end
return '0x'+ right(CAST(@val ue AS VARCHAR(514)), @len*2)
end
GO

-- That's slightly faster than this version, but has the same effect:
alter function dbo.ufn_vbintoh exstr (
@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(@vbi n_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)*40 96
+(@x&0x0F00)*25 6
+(@x&0x00F0)*16
+(@x&0x000F)
set @x = @x + 0x3030303030303 030
+( (@x+0x060606060 6060606)/16
&0x010101010101 0101 )*7
select @value = @value + CAST(@x AS BINARY(8))
set @loop = @loop + 4
end
return '0x'+ right(CAST(@val ue AS VARCHAR(514)), @len*2)
end
GO

-- My timing code:
-- scan 160000 rows
declare @t datetime set @t=getdate()
select max(dbo.ufn_vbi ntohexstr(cast( a.status as
binary(4)))+dbo .ufn_vbintohexs tr(cast(b.statu s as binary(4))))
from sysobjects a,sysobjects b
print datediff(ms,@t, getdate())
-- Clinton's original: 13100 13246
-- substring: 7570 6980 6880
-- big substring: 6220 5696 5740
-- arith 8: 6000 5996
-- CHAR to NCHAR: 6050 5766 5760

-- For BINARY(8), the CHAR to NCHAR version (with lots of CASTs)
-- is about the same speed as "big substring", but should be faster for
larger varbinary's
-- "big substring" just uses this in the inner loop, and processes one byte
at a time:
-- SUBSTRING('0001 0203[...]FDFEFF', SUBSTRING(@vbin ,@loop,1)*2+1, 2)

/*
Note that CASTing from BINARY to INT can be done implicitly, but sometimes
has strange effects:

RIGHT:
SELECT 0x10+1, 0x100000000+cas t(4294967296 as bigint)
----------- --------------------
17 8589934592

WRONG:
SELECT 0x100000000+1, 0x100000000+429 4967296
----------- -------------
1 4294967296

In both of these cases, the implicit cast was apparently to INT.

4294967296 is a DECIMAL!

SELECT CAST(4294967296 as VARBINARY)
--------------------------------------------------------------
0x0A00000100000 00001000000

That is a DECIMAL.

SELECT CAST(65536*CAST (65536 AS BIGINT)AS VARBINARY)
--------------------------------------------------------------
0x0000000100000 000

That is a BIGINT. INT * BIGINT = BIGINT
*/

-- Anyway, here are my two versions of HexToInt, first with arithmetic,
second with the CHAR to NCHAR cast:

CREATE FUNCTION dbo.HexToINT
(
@Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
IF @Value LIKE '%[^0-9A-Fa-f]%'
RETURN NULL
-- RAISERROR('Inva lid hexadecimal string %s',16,1,@Value )
-- RAISERROR (21344, 16, 1, 'Value')
-- CANT RAISERROR in a UDF
-- RETURN CAST(256 AS TINYINT) --RAISERROR alternative?
DECLARE @I BIGINT
SET @I = CAST(CAST(RIGHT ( UPPER( '00000000' + @Value ) , 8 )
AS BINARY(8)) AS BIGINT) - CAST(0x30303030 30303030 AS BIGINT)
SET @I=@I-((@I/16)&CAST(0x0101 010101010101 AS BIGINT))*7
RETURN CAST( CAST(
(@I&15)
+((@I/16)&240)
+((@I/256)&3840)
+((@I/4096)&61440)
+((@I/65536)&983040)
+((@I/1048576)&157286 40)
+((@I/16777216)&25165 8240)
+(@I/CAST(0x01000000 00000000 AS BIGINT)*2684354 56)
AS BINARY(4)) AS INT)
END
GO

CREATE FUNCTION dbo.HexToINT2
(
@Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
IF @Value LIKE '%[^0-9A-Fa-f]%'
RETURN NULL
DECLARE @I BIGINT
SET @I = CAST(CAST(RIGHT ( UPPER( '00000000' + @Value ) , 8 )
AS BINARY(8)) AS BIGINT) - CAST(0x30303030 30303030 AS BIGINT)
SET @I = @I-((@I/16)&CAST(0x0101 010101010101 AS BIGINT))*7
RETURN CAST(CAST(
16*CAST(CAST(CA ST(CAST(CAST(
@I & CAST(0x0F000F00 0F000F00 AS BIGINT)
AS BINARY(8))AS NCHAR(4))AS CHAR(4))AS BINARY(4))AS BIGINT)
+ CAST(CAST(CAST( CAST(CAST((@I & CAST(0x000F000F 000F000F AS BIGINT))*256 AS
BINARY(8))AS NCHAR(4))AS CHAR(4))AS BINARY(4))AS INT)
AS BINARY(4))AS INT)
END
GO

SELECT
dbo.HexToINT2(' 8BCDEF12')
SELECT
dbo.HexToINT2(' 0ABC') ,
dbo.HexToINT2(' 7FFF') ,
dbo.HexToINT2(' 0FFF') ,
dbo.HexToINT2(' 0') AS MinValue,
dbo.HexToINT2(' 7FFFFFFF') AS MaxValue,
dbo.HexToINT2(' 80000000') AS MaxNeg,
dbo.HexToINT2(' FFFFFFFF') AS negone

select dbo.HexToINT2(' ffffffff')
SELECT
dbo.HexToINT2(' 8BCDEF12')
go

-- My timing code (since I found that SET STATISTICS TIME ON affects timing
results) :

drop table randhex
go
select top 1000000 CAST(substring( '0123456789ABCD EF', c.id&7+1, 1)
+substring('012 3456789ABCDEF', c.id/16&15+1, 1)
+substring('012 3456789ABCDEF', c.id/256&15+1, 1)
+substring('012 3456789ABCDEF', c.id/4096&15+1, 1) AS CHAR(4))
AS randhex
into tempdb..randhex
from sysobjects a,sysobjects b,sysobjects c

go
--set statistics time off
declare @t datetime set @t=getdate()
select sum(cast(dbo.He xToINT(randhex) as bigint))
from tempdb..randhex
print datediff(ms,@t, getdate())
go

--You'll probably find HexToINT is slightly faster than HexToINT2 (on Athlon
XP)