Fun with CAST! (Optimized SQLServerCentral 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_vbintohexstr(0x123456789abcdef1234)
select 0x123456789abcdef1234
create function dbo.ufn_vbintohexstr (
@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(@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 = 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 + 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
-- That's slightly faster than this version, but has the same effect:
alter 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
-- My timing code:
-- scan 160000 rows
declare @t datetime set @t=getdate()
select max(dbo.ufn_vbintohexstr(cast(a.status as
binary(4)))+dbo.ufn_vbintohexstr(cast(b.status 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('00010203[...]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+cast(4294967296 as bigint)
----------- --------------------
17 8589934592
WRONG:
SELECT 0x100000000+1, 0x100000000+4294967296
----------- -------------
1 4294967296
In both of these cases, the implicit cast was apparently to INT.
4294967296 is a DECIMAL!
SELECT CAST(4294967296 as VARBINARY)
--------------------------------------------------------------
0x0A0000010000000001000000
That is a DECIMAL.
SELECT CAST(65536*CAST(65536 AS BIGINT)AS VARBINARY)
--------------------------------------------------------------
0x0000000100000000
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('Invalid 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(0x3030303030303030 AS BIGINT)
SET @I=@I-((@I/16)&CAST(0x0101010101010101 AS BIGINT))*7
RETURN CAST( CAST(
(@I&15)
+((@I/16)&240)
+((@I/256)&3840)
+((@I/4096)&61440)
+((@I/65536)&983040)
+((@I/1048576)&15728640)
+((@I/16777216)&251658240)
+(@I/CAST(0x0100000000000000 AS BIGINT)*268435456)
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(0x3030303030303030 AS BIGINT)
SET @I = @I-((@I/16)&CAST(0x0101010101010101 AS BIGINT))*7
RETURN CAST(CAST(
16*CAST(CAST(CAST(CAST(CAST(
@I & CAST(0x0F000F000F000F00 AS BIGINT)
AS BINARY(8))AS NCHAR(4))AS CHAR(4))AS BINARY(4))AS BIGINT)
+ CAST(CAST(CAST(CAST(CAST((@I & CAST(0x000F000F000F000F 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('0123456789ABCDEF', c.id&7+1, 1)
+substring('0123456789ABCDEF', c.id/16&15+1, 1)
+substring('0123456789ABCDEF', c.id/256&15+1, 1)
+substring('0123456789ABCDEF', 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.HexToINT(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)
