473,287 Members | 1,492 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,287 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 3561

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: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
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...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.