March 14, 2023 at 11:51 pm
Hi,
Need to convert a hexadecimal to decimal/numeric with 20 digits, but due to bigint limitation i get a negative value that doesn't correspond to the exact value, is there any way to overcome this limitation?
select CAST(CONVERT(VARBINARY, 'D002336AE90E2D93', 2) AS bigint)
Result: -3458145029597549165
The convertion should give the value 14988599044112002451.
Thanks in advance.
March 15, 2023 at 1:39 am
have a look at the function on last post from Steve (sgmunson) at https://www.sqlservercentral.com/forums/topic/how-can-convert-hex-value-to-decimal-in-sql-server-2012
March 15, 2023 at 1:27 pm
That's because bigint stores integers as Two's complement, negative numbers need 2^64 (18446744073709551616) added to them to represent the number as an unsigned int.
SELECT CAST(CONVERT(VARBINARY, 'D002336AE90E2D93', 2) AS bigint) + 18446744073709551616;
Not sure how you are using this but you could test if the result is negative, if it is add 18446744073709551616 to the result.
March 15, 2023 at 7:37 pm
That's because bigint stores integers as Two's complement, negative numbers need 2^64 (18446744073709551616) added to them to represent the number as an unsigned int.
SELECT CAST(CONVERT(VARBINARY, 'D002336AE90E2D93', 2) AS bigint) + 18446744073709551616;Not sure how you are using this but you could test if the result is negative, if it is add 18446744073709551616 to the result.
Man, that's clever. Too bad it doesn't work for everything. For example...
SELECT CAST(CONVERT(VARBINARY, '01', 2) AS BIGINT) + 18446744073709551616;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2023 at 8:27 pm
I tested Steve Munson's good code. After changing the input variable from 22 to 30 characters max (15 bytes hex), it works fine but it's a bit greedy on CPU. Here are the run results with his code on 1 Million random conversions of 30 digit hex values.
SQL Server Execution Times:
CPU time = 153281 ms, elapsed time = 14549 ms.
Here's my submission for a possible solution on this thread. Details are in the code.
CREATE FUNCTION dbo.HexStringToDecValue
/**********************************************************************************************************************
Purpose:
Convert a string of hex digits (0-9, A-F) without a leading '0X', convert the hex to a DECIMAL(38,0) value.
(See the "Programmer Notes" section for limitations)
-----------------------------------------------------------------------------------------------------------------------
Usage Examples:
--===== String literal
SELECT * FROM dbo.HexStringToDecValue('D002336AE90E2D93')
;
--===== Single variable
DECLARE @HexString VARCHAR(36) = 'D002336AE90E2D93';
SELECT * FROM dbo.HexStringToDecValue(@HexString)
;
--===== With table source
SELECT h2d.DecValue
FROM dbo.SomeTable st
CROSS APPLY FROM dbo.HexStringToDecValue(st.SomeHexStringColumn) h2d
;
-----------------------------------------------------------------------------------------------------------------------
Programmer Notes:
1. The Hex string must have an even number of hex digits or a conversion error will be returned.
2. Maximum number of Hex digits is 30. More will cause an Arithmetic Overflow error.
3. This function only returns positive numbers. The Sign Bit is not recognized.
4. This is a high-performance iTVF (inline Table Valued Function) that is being used as an iSF (inline Scalar Funtion)
and should be used in the FROM clause or a CROSS APPLY.
5. This function has no dependencies.
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 15 Mar 2023 - Jeff Moden
- Initial creation and partial unit test.
**********************************************************************************************************************/--===== I/O for this function
(@HexString VARCHAR(40)) --Headroom left to force error if hex is too large (>15 bytes or 30 hex digits)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
Tally AS
(--===== Micro-Tally "Table" count-by-2 up to max length of input.
SELECT BytePos FROM (VALUES (1),(3),(5),(7),(9),(11),(13),(15),(17),(19),(21),(23),(25),(27),(29)
)v(BytePos))
SELECT DecValue = SUM(--== Treats each byte as a power of 256 right to left starting at 0.
POWER(CONVERT(DECIMAL(38),256),LEN(@HexString)/2-(t.BytePos/2)-1)
*CONVERT(INT,CONVERT(BINARY(1),SUBSTRING(@HexString,t.BytePos,2),2))
)
FROM Tally t
WHERE t.BytePos <= LEN(@HexString)
;
GO
For the same million row test, here are the CPU and duration results... it didn't need to go parallel.
SQL Server Execution Times:
CPU time = 9469 ms, elapsed time = 9692 ms.
If you're interesting in testing more code, here's a suggested test table. You can get the fnTally function from the first link in my signature line below. If you're using 2022, you can certain sub GENERATE_SERIES(1,1000000) for the call to fnTally.
SELECT HexString = RIGHT(REPLACE(CONVERT(VARCHAR(36),NEWID()),'-',''),30)
INTO #MyHead
FROM dbo.fnTally(1,1000000)
;
My basic test harness looks like this...
DECLARE @BitBucket DECIMAL(38);
SET STATISTICS TIME,IO ON;
SELECT @BitBucket = h2d.PutYourReturnColumnNameHere
FROM #MyHead st
CROSS APPLY dbo.PutYourFunctionHere(st.HexString) h2d
;
SET STATISTICS TIME,IO OFF;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2023 at 8:47 pm
Jonathan AC Roberts wrote:That's because bigint stores integers as Two's complement, negative numbers need 2^64 (18446744073709551616) added to them to represent the number as an unsigned int.
SELECT CAST(CONVERT(VARBINARY, 'D002336AE90E2D93', 2) AS bigint) + 18446744073709551616;Not sure how you are using this but you could test if the result is negative, if it is add 18446744073709551616 to the result.
Man, that's clever. Too bad it doesn't work for everything. For example...
SELECT CAST(CONVERT(VARBINARY, '01', 2) AS BIGINT) + 18446744073709551616;
What result are you expecting? It's positive so no need to add 18446744073709551616 to it.
DECLARE @x varchar(50) = '01'
;WITH CTE AS
(
SELECT CAST(CONVERT(VARBINARY, @x, 2) AS BIGINT) x
)
SELECT IIF(x > 0, x, x + 18446744073709551616)
FROM CTE
;
March 15, 2023 at 8:52 pm
Jeff Moden wrote:Jonathan AC Roberts wrote:That's because bigint stores integers as Two's complement, negative numbers need 2^64 (18446744073709551616) added to them to represent the number as an unsigned int.
SELECT CAST(CONVERT(VARBINARY, 'D002336AE90E2D93', 2) AS bigint) + 18446744073709551616;Not sure how you are using this but you could test if the result is negative, if it is add 18446744073709551616 to the result.
Man, that's clever. Too bad it doesn't work for everything. For example...
SELECT CAST(CONVERT(VARBINARY, '01', 2) AS BIGINT) + 18446744073709551616;What result are you expecting? It's positive so no need to add 18446744073709551616 to it.
DECLARE @x varchar(50) = '01'
;WITH CTE AS
(
SELECT CAST(CONVERT(VARBINARY, @x, 2) AS BIGINT) x
)
SELECT IIF(x > 0, x, x + 18446744073709551616)
FROM CTE
;
Ah, sorry, Jonathan. I was expecting the code to work without modification. I'll drop that bad boy into a function and test it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2023 at 9:12 pm
Serious "chicken dinner" there for performance, Jonathan... here are the stats from a test on a million rows of 16 hex digits (8 bytes)...
SQL Server Execution Times:
CPU time = 562 ms, elapsed time = 573 ms.
Nasty fast!
And, I checked it for accuracy and it's spot on.
The only drawbacks are that it is limited to 16 hex digits and provides no warning of incorrect answers if you exceed that but this is definitely an awesome math trick. Thanks!
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2023 at 9:25 pm
Serious "chicken dinner" there for performance, Jonathan... here's stats from a test on a million rows of 16 hex digits (8 bytes)...
SQL Server Execution Times:
CPU time = 562 ms, elapsed time = 573 ms.Nasty fast!
And, I checked it for accuracy and it's spot on.
The only drawbacks are that it is limited to 16 hex digits and provides no warning of incorrect answers if you exceed that but this is definitely an awesome math trick. Thanks!
I think I should have had x >= 0 not x > 0.
March 15, 2023 at 9:26 pm
Keep it simple
DECLARE @sample TABLE
(
BinaryValue VARBINARY(12)
);
INSERT @sample
VALUES (0x10101010FFFFFFFEFFFFFFFA), (0xD002336AE90E2D93);
SELECT BinaryValue,
CAST(18446744073709551616 AS DECIMAL(38, 0)) * CAST(CAST(SUBSTRING(BinaryValue, DATALENGTH(BinaryValue) - 11, 4) AS BIGINT) AS DECIMAL(38, 0))
+ CAST(4294967296 AS DECIMAL(38, 0)) * CAST(CAST(SUBSTRING(BinaryValue, DATALENGTH(BinaryValue) - 7, 4) AS BIGINT) AS DECIMAL(38, 0))
+ CAST(CAST(SUBSTRING(BinaryValue, DATALENGTH(BinaryValue) - 3, 4) AS BIGINT) AS DECIMAL(38, 0)) AS decimal_value
FROM @sample
N 56°04'39.16"
E 12°55'05.25"
March 15, 2023 at 9:33 pm
DECLARE @sample TABLE
(
BinaryValue VARBINARY(12)
);
INSERT @sample
VALUES (0xFFFFFFFFFFFFFFFFFFFFFFFF), (0xD002336AE90E2D93), (0xFFFFFFFFFFFFFFFF);
SELECT BinaryValue,
CAST(18446744073709551616 AS DECIMAL(38, 0)) * CAST(CAST(SUBSTRING(BinaryValue, DATALENGTH(BinaryValue) - 11, 4) AS BIGINT) AS DECIMAL(38, 0))
+ CAST(4294967296 AS DECIMAL(38, 0)) * CAST(CAST(SUBSTRING(BinaryValue, DATALENGTH(BinaryValue) - 7, 4) AS BIGINT) AS DECIMAL(38, 0))
+ CAST(CAST(SUBSTRING(BinaryValue, DATALENGTH(BinaryValue) - 3, 4) AS BIGINT) AS DECIMAL(38, 0)) AS decimal_value
FROM @sample
N 56°04'39.16"
E 12°55'05.25"
March 15, 2023 at 9:59 pm
Keep it simple
DECLARE @sample TABLE
(
BinaryValue VARBINARY(12)
);
INSERT @sample
VALUES (0x10101010FFFFFFFEFFFFFFFA), (0xD002336AE90E2D93);
SELECT BinaryValue,
CAST(18446744073709551616 AS DECIMAL(38, 0)) * CAST(CAST(SUBSTRING(BinaryValue, DATALENGTH(BinaryValue) - 11, 4) AS BIGINT) AS DECIMAL(38, 0))
+ CAST(4294967296 AS DECIMAL(38, 0)) * CAST(CAST(SUBSTRING(BinaryValue, DATALENGTH(BinaryValue) - 7, 4) AS BIGINT) AS DECIMAL(38, 0))
+ CAST(CAST(SUBSTRING(BinaryValue, DATALENGTH(BinaryValue) - 3, 4) AS BIGINT) AS DECIMAL(38, 0)) AS decimal_value
FROM @sample
How is that "simple", Peter?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2023 at 2:04 am
Serious "chicken dinner" there for performance, Jonathan... here are the stats from a test on a million rows of 16 hex digits (8 bytes)...
SQL Server Execution Times:
CPU time = 562 ms, elapsed time = 573 ms.Nasty fast!
And, I checked it for accuracy and it's spot on.
The only drawbacks are that it is limited to 16 hex digits and provides no warning of incorrect answers if you exceed that but this is definitely an awesome math trick. Thanks!
Yes, 16 hex digits is a 64-bit word, so possibly all the OP requires as they were casting it as a bigint.
Convert does just truncate the answer if it is too long with no error.
March 16, 2023 at 5:44 am
Heh... unfortunately, I know that problem all too well because some folks at work wrote a SCALAR function to solve that issue and they use it just about everywhere.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply