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