UNHEX for Integers in MySQL

The following is a very, very specific subject matter, but I wish this blog post had existed when I was looking for the solution to a problem I was having. Most likely it has no interest to anyone who is not specifically looking for it, so feel free to stop reading...

MySQL has a few built-in functions for handling binary data. One of them is HEX which converts any data into hexadecimal representation. The function which you would expect to do the opposite (as the manual states) is UNHEX which takes a hexadecimal representation and turns it into characters.

So, if you try to do:
You get an "a" back. But if you try that on a integer:
You get back the char corresponding to the ASCII value of 1, which is not what was intended.
The correct way to do it, and the real opposite of HEX is the CONV function which converts betweens bases:
SELECT CONV(HEX(1), 16, 10);
This time the result is the number "1" as expected.

No comments:

Post a Comment