# Hexa 2 decimal 20 digits

• 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.

• 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

• 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.

• 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;`

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• 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 variableDECLARE @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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• 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;`
• Jonathan AC Roberts wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• 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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden wrote:

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.

• Keep it simple

`DECLARE @sample TABLE        (                BinaryValue VARBINARY(12)        );INSERT  @sampleVALUES  (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_valueFROM    @sample`

N 56°04'39.16"
E 12°55'05.25"

• `DECLARE @sample TABLE        (                BinaryValue VARBINARY(12)        );INSERT  @sampleVALUES  (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_valueFROM    @sample`

N 56°04'39.16"
E 12°55'05.25"

• SwePeso wrote:

Keep it simple

`DECLARE @sample TABLE        (                BinaryValue VARBINARY(12)        );INSERT  @sampleVALUES  (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_valueFROM    @sample`

How is that "simple", Peter?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden wrote:

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.

• Jonathan AC Roberts wrote:

Convert does just truncate the answer if it is too long with no error.

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.