How the UNHEX() Function Works in MySQL

In MySQL, the UNHEX() function allows you to “unhex” a string in MySQL. In other words, it allows you to convert a hexadecimal value to a human-readable string.

Specifically, the function 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.

Syntax

The syntax goes like this:

UNHEX(str)

Where str is the string to unhex.

Example 1 – Basic Usage

Here’s a basic example:

SELECT UNHEX('436F636F6E75742070616C6D') AS Unhexed;

Result:

+--------------+
| Unhexed      |
+--------------+
| Coconut palm |
+--------------+

So I provided a hex value as an argument, and the function unhexed that value and returned the result (which is a binary string).

Example 2 – Convert a String to Hexadecimal

We can also do the reverse by using the HEX() function to convert the string into a hexadecimal value:

SELECT HEX('Coconut palm') AS Hexed;

Result:

+--------------------------+
| Hexed                    |
+--------------------------+
| 436F636F6E75742070616C6D |
+--------------------------+

Therefore, we could nest one function within the other, and we’d simply get our argument returned:

SELECT 
  UNHEX(HEX('Coconut palm')) AS 'Result 1',
  HEX(UNHEX('436F636F6E75742070616C6D')) AS 'Result 2';

Result:

+--------------+--------------------------+
| Result 1     | Result 2                 |
+--------------+--------------------------+
| Coconut palm | 436F636F6E75742070616C6D |
+--------------+--------------------------+

Example 3 – Invalid Hexadecimal Characters

The argument must contain valid hexadecimal characters. If any of the characters are not valid hex characters, the result will be NULL:

SELECT 
  UNHEX(' '),
  UNHEX('_xyz');

Result:

+------------+---------------+
| UNHEX(' ') | UNHEX('_xyz') |
+------------+---------------+
| NULL       | NULL          |
+------------+---------------+

Example 4 – Numeric Arguments

The UNHEX() function doesn’t work on the hexadecimal equivalents of numeric values.

Here’s an example of what I mean:

SELECT 
  HEX(1234),
  UNHEX('4D2');

Result:

+-----------+--------------+
| HEX(1234) | UNHEX('4D2') |
+-----------+--------------+
| 4D2       | ?            |
+-----------+--------------+

Another way to demonstrate this would be:

SELECT UNHEX(HEX(1234));

Result:

+------------------+
| UNHEX(HEX(1234)) |
+------------------+
| ?                |
+------------------+

So what this shows us is that the hexadecimal equivalent of 1234 is 4D2, and the UNHEX() function can’t handle that value.

In such cases, you can use the CONV() function instead:

SELECT 
  HEX(1234),
  CONV('4D2', 16, 10),
  CONV(HEX(1234), 16, 10);

Result:

+-----------+---------------------+-------------------------+
| HEX(1234) | CONV('4D2', 16, 10) | CONV(HEX(1234), 16, 10) |
+-----------+---------------------+-------------------------+
| 4D2       | 1234                | 1234                    |
+-----------+---------------------+-------------------------+

In this example, we use CONV() to convert the value from base-16 (hexadecimal) to base-10 (decimal).

How to “Unhex” a Number in MySQL

In MySQL, you can unhex a string using the UNHEX() function. But you can’t unhex a number with that function.

To unhex a number in MySQL, use the CONV() function instead.

The CONV() function allows you to convert numeric values between different numbering systems. For example, you can convert between say decimal and binary, octal to decimal, or, more relevant to this article, between hexadecimal and decimal.

Syntax

The syntax goes like this:

CONV(N,from_base,to_base)

Where N is the number you want to convert, from_base is the base that the number is in, and to_base is the base you want to convert it to.

Example

Here’s an example to demonstrate:

SELECT CONV('F',16,10);

Result:

+-----------------+
| CONV('F',16,10) |
+-----------------+
| 15              |
+-----------------+

In this case, we convert the number F from base 16 (hexadecimal) to base 10 (decimal). So we can see that F in hexadecimal equals 15 in decimal.

Here’s another example using a larger number:

SELECT CONV('FCA3B',16,10);

Result:

+---------------------+
| CONV('FCA3B',16,10) |
+---------------------+
| 1034811             |
+---------------------+

3 Ways to “Unhex” a String in MySQL

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 0xnotation, 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).