MySQL includes various functions that can assist you when working with hexadecimal values. One of the most obvious functions is the HEX()
function, which enables you to convert a string into its hexadecimal equivalent.
However, there may be times where you want to convert a hexadecimal string back to a more readable string. That’s what this article is about.
Here are three ways to “unhex” a string in MySQL:
- The
UNHEX()
function - The
X
hexadecimal notation - The
0x
notation
Below are examples of each of these methods.
The UNHEX() Function
This is a string function built specifically for “unhexing” a hexadecimal string.
Example:
SELECT UNHEX('4361747320616E6420646F6773');
Result:
+-------------------------------------+ | UNHEX('4361747320616E6420646F6773') | +-------------------------------------+ | Cats and dogs | +-------------------------------------+
The way UNHEX()
works is that it interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.
The X Notation
An alternative way to unhex a string is to use the X
notation.
Example:
SELECT X'4361747320616E6420646F6773';
Result:
+-------------------------------+ | X'4361747320616E6420646F6773' | +-------------------------------+ | Cats and dogs | +-------------------------------+
The X
notation is based on standard SQL. This notation is case-insensitive, so it doesn’t matter whether you use an uppercase X
or lowercase. This is in contrast to the 0x
notation, which is case-sensitive.
Note that the X
notation requires an even number of digits. If you have an odd number of digits, you can pad it with a leading zero.
The 0x Notation
And the third way to unhex a string is to use the 0x
notation.
Example:
SELECT 0x4361747320616E6420646F6773;
Result:
+------------------------------+ | 0x4361747320616E6420646F6773 | +------------------------------+ | Cats and dogs | +------------------------------+
The 0x
notation is based on ODBC, for which hexadecimal strings are often used to supply values for BLOB columns. As mentioned, the 0x
notation is case-sensitive, so it won’t work if you use an uppercase X
.
The 0x
notation does work with an odd number of digits, but only because it will interpret any odd number as having a leading zero (thus making it even).
Unhex a Number
Note that he HEX()
function doesn’t work on numbers. To do that, use the CONV()
function instead (see How to Unhex a Number in MySQL).