473,326 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

HexToInt, vbintoHexStr - fun with CAST

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)

Jul 23 '05 #1
0 3565

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Aaron W. West | last post by:
Inspired/challenged by Hans Lindgren's stored procedures of these same names posted on SQLServerCentral, I created these. Note that they produce strange results on non-hexadecimal strings, and may...
3
by: Mike | last post by:
I am using MS-Access as a front end for my MS-SQL DB. I have a sql view that uses the following: SELECT TOP 100 PERCENT RECID, PATNUMBER AS , SVCCODE AS , QTY, PROF_CHRGS AS , AMOUNT,...
4
by: Ray | last post by:
When a single-bit bitfield that was formed from an enum is promoted/cast into an integer, does ANSI C say anything about whether that integer should be signed or unsigned? SGI IRIX cc thinks it is...
17
by: Hazz | last post by:
In this sample code of ownerdraw drawmode, why does the '(ComboBox) sender' line of code need to be there in this event handler? Isn't cboFont passed via the managed heap, not the stack, into this...
3
by: mra | last post by:
I want to cast an object that I have created from a typename to the corresponding type. Can anycone tell me how to do this? Example: //Here, Create the object of type "MyClass" object...
5
by: Nick Flandry | last post by:
I'm running into an Invalid Cast Exception on an ASP.NET application that runs fine in my development environment (Win2K server running IIS 5) and a test environment (also Win2K server running IIS...
9
by: Frederick Gotham | last post by:
Let's assume that we're working on the following system: CHAR_BIT == 8 sizeof( char* ) == 4 (i.e. 32-Bit) Furthermore, lets assume that the memory addresses are distributed as follows: ...
5
by: Frederick Gotham | last post by:
Before I begin, here's a list of assumptions for this particular example: (1) unsigned int has no padding bits, and therefore no invalid bit- patterns or trap representations. (2) All types have...
7
by: * Tong * | last post by:
Hi, I couldn't figure out how to properly type cast in this case: $ cat -n type_cast.c 1 #include <stdio.h> 2 3 typedef unsigned char Byte; 4 typedef signed char Small_Int; 5
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.