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.

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?

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:

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.