How UNHEX() Works in MariaDB

In MariaDB, UNHEX() is a built-in string function that performs the inverse operation of the HEX() function.

Whereas, the HEX() function returns a hexadecimal representation of a given string, the UNHEX() function returns the string representation of a given hexadecimal value.

Specifically, UNHEX() interprets each pair of hexadecimal digits in its argument as a number, and then converts it to the character represented by the number.

The result is returned as a binary string.

Syntax

The syntax goes like this:

UNHEX(str)

Where str is the hexadecimal value to be “unhexed”.

Example

Here’s a basic example:

SELECT UNHEX('61');

Result:

+-------------+
| UNHEX('61') |
+-------------+
| a           |
+-------------+

The hexadecimal representation of the lowercase letter a is 61, and so by passing 61 to the UNHEX() function, we get the lowercase letter a.

Here’s an example that runs through a few more hexadecimal values:

SELECT 
    UNHEX('61'),
    UNHEX('6161'),
    UNHEX('41'),
    UNHEX('4141'),
    UNHEX('62'),
    UNHEX('6262'),
    UNHEX('42'),
    UNHEX('4242'),
    UNHEX('436174'),
    UNHEX('446F67'),
    UNHEX('50617773202620436C617773'),
    UNHEX('5A65627261');

Result (using vertical output):

                      UNHEX('61'): a
                    UNHEX('6161'): aa
                      UNHEX('41'): A
                    UNHEX('4141'): AA
                      UNHEX('62'): b
                    UNHEX('6262'): bb
                      UNHEX('42'): B
                    UNHEX('4242'): BB
                  UNHEX('436174'): Cat
                  UNHEX('446F67'): Dog
UNHEX('50617773202620436C617773'): Paws & Claws
              UNHEX('5A65627261'): Zebra

Compared to HEX()

Here’s an example that nests HEX() inside UNHEX() to return the original string:

SELECT 
    HEX('Homer Jay'),
    UNHEX(HEX('Homer Jay'));

Result:

+--------------------+-------------------------+
| HEX('Homer Jay')   | UNHEX(HEX('Homer Jay')) |
+--------------------+-------------------------+
| 486F6D6572204A6179 | Homer Jay               |
+--------------------+-------------------------+

However, bear in mind that UNHEX() returns its result as a binary string:

SELECT 
    COLLATION('Homer Jay') AS "Literal",
    COLLATION(HEX('Homer Jay')) AS "HEX()",
    COLLATION(UNHEX(HEX('Homer Jay'))) AS "UNHEX()";

Result:

+-----------------+-----------------+---------+
| Literal         | HEX()           | UNHEX() |
+-----------------+-----------------+---------+
| utf8_general_ci | utf8_general_ci | binary  |
+-----------------+-----------------+---------+

Null Arguments

Passing null to UNHEX() results in null:

SELECT UNHEX(null);

Result:

+-------------+
| UNHEX(null) |
+-------------+
| NULL        |
+-------------+

Passing No Arguments

Calling UNHEX() without passing any arguments results in an error:

SELECT UNHEX();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'UNHEX'