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