 # HexToInt

,

Challenged by Hans Lindgren's stored procedures of the same name, I created this. Note that it produces strange results on non-hexadecimal strings, overflows at 0x80000000, and could have issues with byte-ordering on some architectures.

How does it 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 nybbles into the proper places and add.

```-- By Aaron West, 5/4/2005
-- This version allows negative numbers
CREATE FUNCTION dbo.HexToINT
(
@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(
(@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

SELECT
dbo.HexToINT('0ABC') ,
dbo.HexToINT('7FFF') ,
dbo.HexToINT('0FFF') ,
dbo.HexToINT('0')    AS Zero,
dbo.HexToINT('7FFFFFFF') AS MaxValue,
dbo.HexToINT('80000000') AS MaxNeg,
dbo.HexToINT('FFFFFFFF') AS NegOne```