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:


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


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');

�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);


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


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

hello, bumping this thread