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).