MySQL Hex to Dec conversion (issue with large numbers)

jjj
January 1, 2023
202 views
0 votes
2 Answers

I have an input column in my MySQL database that ends with the 64-character long hexadecimal strings.

Example:
0000000000000000000000000000000000000000019d971e4fe8401e74000000

I am using the following SUBSTRING(input, 139, 64) to get it out and then trim leading zeros from the string using TRIM(LEADING ‘0’ FROM and finally CONV(x, 16,10) to convert it from HEX to Decimal value.

The final select statement looks like this:

SELECT CONV(TRIM(LEADING '0' FROM SUBSTRING(input, 139, 64)), 16, 10) AS burning
FROM db

Everything is fine when the string looks like this:
80
It’s converted to Decimal value:
128

Or when it looks like this:
1b8
to a correct decimal value of: 440

However, when the string looks like this:
19d971e4fe8401e74000000
CONV converts it into 18446744073709551615, which is a wrong hex to dec conversion.
The correct return should be: 500000000000000000000000000

What am I doing wrong? Is there some limit in CONV that breaks the function?

Tags: mysql

Answers

From the MySQL documentation:

"CONV() works with 64-bit precision"

Thats means that a value which exceeds 64-bit will be truncated:

select (conv("19d971e4fe8401e74000000",16,10))G
*************************** 1. row ***************************
(conv("19d971e4fe8401e74000000",16,10)): 18446744073709551615
1 row in set, 1 warning (0,001 sec)

show warningsG
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DECIMAL value: '19d971e4fe8401e74000000'
1 row in set (0,001 sec)

select hex(18446744073709551615)G
*************************** 1. row ***************************
hex(18446744073709551615): FFFFFFFFFFFFFFFF
1 row in set (0,001 sec)

SET @val = '19d971e4fe8401e74000000';

WITH RECURSIVE
cte AS (
  SELECT CONV(RIGHT(@val, 8), 16, 10) part,
         LEFT(@val, GREATEST(LENGTH(@val) - 8, 0)) rest,
         CAST(1 AS DECIMAL(40,0)) multiplier
  UNION ALL
  SELECT CONV(RIGHT(rest, 8), 16, 10),
         LEFT(rest, GREATEST(LENGTH(rest) - 8, 0)),
         CAST(multiplier AS DECIMAL(40,0)) * CAST(POW(2, 32) AS DECIMAL(40,0))
  FROM cte
  WHERE rest <> ''
)
SELECT SUM(CAST(CAST(part AS DECIMAL(40,0)) * CAST(multiplier AS DECIMAL(40,0)) AS DECIMAL(40,0))) result
FROM cte