Converting HEX to INT in SELECT Statements

Hi all,

Is there a particular syntax for converting HEX to INT in a select statement? It seems what I find on Google for other platforms (MS SQL Server, mySQL) don’t particularly work on MemSQL.

For example:
SELECT ‘51afc4e36c3e357’ FROM table;

Trying to get MemSQL to show:
367883732440572759

Thanks

Figured this out with using:
SELECT CONVERT(x'000000000000000000000000000000000000000000000000051afc4e36c3e357', UNSIGNED);

Returns:
367883732440572759


I’d like to repeat the above steps using data from existing columns. Since I cannot add X to a column name, like I can with a string variable x'0000, I found using the UNHEX function temporarily works. Here are the two values side-by-side:
SELECT x'000000000000000000000000000000000000000000000000051afc4e36c3e357', UNHEX('000000000000000000000000000000000000000000000000051afc4e36c3e357');

Returns:
�N6��W | �N6��W


To ensure this HEX() function still equals the x’0000’ string, I ran the boolean which returns a value of 1
SELECT x'000000000000000000000000000000000000000000000000051afc4e36c3e357' = UNHEX('000000000000000000000000000000000000000000000000051afc4e36c3e357');


Now if I want to convert the below to decimal, I have no problems:
SELECT CONVERT(x'000000000000000000000000000000000000000000000000051afc4e36c3e357', UNSIGNED);

Returns:
367883732440572759


However the below returns a different value:
SELECT CONVERT(UNHEX('000000000000000000000000000000000000000000000000051afc4e36c3e357'), UNSIGNED);

Returns:
0

How can I get the above to properly show the resulting 367883732440572759 integer?

The conv() builtin is design to convert between bases (you want from base 16 to base 10 here).

MemSQL [(none)]> select conv('51afc4e36c3e357',16,10);
+-------------------------------+
| conv('51afc4e36c3e357',16,10) |
+-------------------------------+
| 367883732440572759            |
+-------------------------------+
1 Like

Thanks, how would I convert larger numbers like

  • CONV(‘62307efe509b14437’, 16, 10)
  • CONV(‘1b1ae4d6e2ef500000’, 16, 10)

Both return 18446744073709551615, yet the correct value for each would be:

  • 113204714325410923575
  • 500000000000000000000

https://www.rapidtables.com/convert/number/hex-to-decimal.html

hello, bumping this thread