Converting HEX to INT in SELECT Statements

Help Procedural SQL
1

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

2

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?

3

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
4

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
5

hello, bumping this thread

6

Hey all, bumping this thread again and would love for someone from the SingleStore team to please respond.

Is there any way to convert large numbers (I’m assuming BIGINT) to HEX and back. If not, if this would be supported in 7.3 and later SingleStore versions.

From a previous post:

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

Hexadecimal to Decimal Converter

Thanks

7

Hi ymonye,

CONV() does return a BIGINT. 18446744073709551615 is the largest bigint value (2^64 - 1). The two values you have don’t fit in a bigint (they are larger), so that is why your running into issues.

I don’t think there is a builtin way to convert a hex value that is larger then a BIGINT to a DECIMAL type capable of storing it.

-Adam