MariaDB UNHEX() Function

In MariaDB, UNHEX() is a built-in string function that converts a string representing a hexadecimal value to bytes and returns the corresponding binary string.

The process of the UNHEX() function is to convert every two digits from the string parameter of the hexadecimal value to bytes, and return all the bytes combined as a binary string.

The UNHEX() function is the inverse of HEX().

MariaDB UNHEX() Syntax

Here is the syntax of the MariaDB UNHEX() function:

UNHEX(string)

Parameters

string

Required. Hexadecimal numeric string. Only 0...9, A...F, or a...f symbols are allowed in hexadecimal value.

If you provide the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'UNHEX'.

Return value

The MariaDB UNHEX() function returns a binary string.

If the argument string is not a legal hexadecimal value, the UNHEX() function will return NULL.

If the argument string is NULL, the UNHEX() function will return NULL.

Since the UNHEX() function returns a binary string, the result displayed on the mysql client may be hexadecimal. Please use at login --binary-as-hex=false to disable displaying binary content as hexadecimal.

MariaDB UNHEX() Examples

Basic example

Here’s a basic example:

SELECT
  UNHEX('61'),
  UNHEX('6162');

Output:

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

HEX() and UNHEX()

The UNHEX() function HEX() is the inverse of a function.

SELECT
  HEX('Hello'),
  UNHEX(HEX('Hello'));

Output:

+--------------+---------------------+
| HEX('Hello') | UNHEX(HEX('Hello')) |
+--------------+---------------------+
| 48656C6C6F   | Hello               |
+--------------+---------------------+

Empty parameter

If the argument is NULL, the UNHEX() function will return NULL.

SELECT UNHEX(null);

Output:

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

Conclusion

The MariaDB UNHEX() function converts strings representing hexadecimal values ​​to bytes and return the corresponding binary strings.