472,353 Members | 1,030 Online

# HexToInt and HexToSmallInt

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 have issues with byte-ordering
in some architectures (but Itanium is little-endian like x86 and x64,
right?).

How do they work? well, the distance between one after '9' (':') and 'A' is
7 in ASCII. Also, if I subtract 48 from an upper-cased hex-digit, digit & 16
will always be equal to 16. So I can mask that bit out, shift it down 4 bits
(/16), multiply by 7, subtract from the original value, and come up with a
value from 0 to 15. This can be done on all 8 digits in parallel, as you can
see below.

I use CAST(CAST('1234ABCD' AS BINARY(8))AS BIGINT) to put the hex value
1234ABCD into a number I can manipulate, then subtract the value '00000000'
(CAST(0x3030303030303030 AS BIGINT)), then mask out the hex overflow bits,
shift right, multiply by 7, subtract to make the values 0x010203040A0B0C0D,
then I shift the bits into the proper places and add.

It's probably easier in assembly language than SQL, but oh well.

It's only about 20% faster than Hans's series of CHARINDEX calls.

CREATE FUNCTION dbo.HexToINT
(
@Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
DECLARE @I BIGINT
SET @I = CAST(CAST(RIGHT( UPPER( '00000000' + @Value ) , 8 )
AS BINARY(8)) AS BIGINT) - 3472328296227680304
SET @I=@I-((@I/16)&CAST(72340172838076673 AS BIGINT))*7
RETURN (
(@I&15)
+((@I/16)&240)
+((@I/256)&3840)
+((@I/4096)&61440)
+((@I/65536)&983040)
+((@I/1048576)&15728640)
+((@I/16777216)&251658240)
+((@I/72057594037927936)*268435456) -- cause an OF if > 0x80000000
)

END
GO

CREATE FUNCTION dbo.HexToSMALLINT
(
@Value VARCHAR(4)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @I INT
SET @I = CAST(CAST(RIGHT( UPPER( '0000' + @Value ) , 4 )
AS BINARY(4)) AS INT) - 808464432
SET @I=@I-(@I&269488144)*7/16
RETURN (
@I&255
+(@I&65280)/16
+(@I&16711680)/256
+(@I&2130706432)/4096
)
END
GO

Jul 23 '05 #1
1 4510 The revised versions below will allow negative numbers, eg.
HexToINT('80000000')

---

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 have issues with byte-ordering
in some architectures (but Itanium is little-endian like x86 and x64,
right?).

How do they work? well, the distance between one after '9' (':') and 'A' is
7 in ASCII. Also, if I subtract 48 from an upper-cased hex-digit, digit & 16
will always be equal to 16. So I can mask that bit out, shift it down 4 bits
(/16), multiply by 7, subtract from the original value, and come up with a
value from 0 to 15. This can be done on all 8 digits in parallel, as you can
see below.

I use CAST(CAST('1234ABCD' AS BINARY(8)) AS BIGINT) to put the
string of hexadecimal digit characters 1234ABCD into a number I can
manipulate,
then subtract the value '00000000' (CAST(0x3030303030303030 AS BIGINT)),
then mask out the hex overflow bits, shift right 4 places (/16), multiply by
7,
subtract to make the values 0x010203040A0B0C0D,
then I shift the bits into the proper places and add. to result in
0x1234ABCD, CAST AS INT.

alter FUNCTION dbo.HexToSMALLINT
(
@Value VARCHAR(4)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @I INT
SET @I = CAST(CAST(RIGHT( UPPER( '0000' + @Value ) , 4 )
AS BINARY(4)) AS INT) - 808464432
SET @I=@I-(@I&269488144)*7/16
RETURN CAST(CAST(
(@I&15)
+((@I/16)&240)
+((@I/256)&3840)
+((@I/4096)&61440)
AS BINARY(2))AS SMALLINT)
END
GO
alter FUNCTION dbo.HexToINT
(
@Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
DECLARE @I BIGINT
SET @I = CAST(CAST(RIGHT( UPPER( '00000000' + @Value ) , 8 )
AS BINARY(8)) AS BIGINT) - 3472328296227680304
SET @I=@I-((@I/16)&CAST(72340172838076673 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)
+(CAST(@I/72057594037927936 AS BIGINT)*268435456)
AS BINARY(4))AS INT)
END
GO

Jul 23 '05 #2

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

### Similar topics

 0 by: Aaron W. West | last post by: Fun with CAST! (Optimized SQLServerCentral script posts) I found some interesting "tricks" to convert binary to hexadecimal and back, which allow... 1 by: db2sysc | last post by: All, We have listed out the INBUILT Function differences between Sybase and DB2. Kindly provide your feedback or corrections and for CHANGES -... 1 by: sakina | last post by: I am trying to change some code from VB to C#. I have a variable called pstrHex with a value of "49799879A7E109F3B4BB4224257E50B5" in VB, the... 1 by: Kemmylinns12 | last post by: Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and... 0 by: Naresh1 | last post by: What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge... 0 by: jalbright99669 | last post by: Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made... 0 by: Matthew3360 | last post by: Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ... 2 by: Matthew3360 | last post by: Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it... 0 by: AndyPSV | last post by: HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable... 0 by: WisdomUfot | last post by: It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific... 0 by: Oralloy | last post by: Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the... 0 by: Carina712 | last post by: Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....