Inspired/challenged by Hans Lindgren's stored procedures of these same names
posted on SQLServerCentra l, 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('1234 ABCD' AS BINARY(8))AS BIGINT) to put the hex value
1234ABCD into a number I can manipulate, then subtract the value '00000000'
(CAST(0x3030303 030303030 AS BIGINT)), then mask out the hex overflow bits,
shift right, multiply by 7, subtract to make the values 0x010203040A0B0 C0D,
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) - 347232829622768 0304
SET @I=@I-((@I/16)&CAST(723401 72838076673 AS BIGINT))*7
RETURN (
(@I&15)
+((@I/16)&240)
+((@I/256)&3840)
+((@I/4096)&61440)
+((@I/65536)&983040)
+((@I/1048576)&157286 40)
+((@I/16777216)&25165 8240)
+((@I/720575940379279 36)*268435456) -- cause an OF if > 0x80000000
)
END
GO
CREATE FUNCTION dbo.HexToSMALLI NT
(
@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
posted on SQLServerCentra l, 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('1234 ABCD' AS BINARY(8))AS BIGINT) to put the hex value
1234ABCD into a number I can manipulate, then subtract the value '00000000'
(CAST(0x3030303 030303030 AS BIGINT)), then mask out the hex overflow bits,
shift right, multiply by 7, subtract to make the values 0x010203040A0B0 C0D,
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) - 347232829622768 0304
SET @I=@I-((@I/16)&CAST(723401 72838076673 AS BIGINT))*7
RETURN (
(@I&15)
+((@I/16)&240)
+((@I/256)&3840)
+((@I/4096)&61440)
+((@I/65536)&983040)
+((@I/1048576)&157286 40)
+((@I/16777216)&25165 8240)
+((@I/720575940379279 36)*268435456) -- cause an OF if > 0x80000000
)
END
GO
CREATE FUNCTION dbo.HexToSMALLI NT
(
@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
Comment