3 Ways to “Unhex” a String in MySQL

by

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