---

{ “title”: “UNHEX”, “language”: “en”, “description”: “The UNHEX function converts a hexadecimal string back to the original string, serving as the inverse operation of the HEX function.” }

Description

The UNHEX function converts a hexadecimal string back to the original string, serving as the inverse operation of the HEX function. This function converts every two hexadecimal characters (0-9, A-F, a-f) into one byte. The UNHEX_NULL function works identically but returns NULL instead of an empty string when encountering invalid input. These functions are useful when handling binary data, encrypted data, or data requiring hexadecimal representation.

:::tip This function is supported since version 3.0.6. :::

Syntax

UNHEX(<str>)

Parameters

Parameter	Description
<str>	The hexadecimal character string

Return Value

Returns VARCHAR type, representing the decoded original string from hexadecimal.

Decoding rules:

• Accepts character range: 0-9, a-f, A-F
• Every two hexadecimal characters convert to one byte
• Result may contain unprintable characters

Special cases (UNHEX):

• If input is NULL, returns empty string
• If string length is 0 or odd, returns empty string
• If contains non-hexadecimal characters, returns empty string

Special cases (UNHEX_NULL):

• If input is NULL, returns NULL
• If string length is 0 or odd, returns NULL
• If contains non-hexadecimal characters, returns NULL

Examples

select unhex('@');

+------------+
| unhex('@') |
+------------+
|            |
+------------+

select unhex_null('@');

+-----------------+
| unhex_null('@') |
+-----------------+
| NULL            |
+-----------------+

select unhex('41');

+-------------+
| unhex('41') |
+-------------+
| A           |
+-------------+

select unhex('4142'), unhex('48656C6C6F');

+---------------+----------------------+
| unhex('4142') | unhex('48656C6C6F')  |
+---------------+----------------------+
| AB            | Hello                |
+---------------+----------------------+

5. NULL handling comparison

SELECT UNHEX(NULL), UNHEX_NULL(NULL);

+-------------+-----------------+
| UNHEX(NULL) | UNHEX_NULL(NULL) |
+-------------+-----------------+
|             | NULL            |
+-------------+-----------------+

6. UTF-8 character decoding

SELECT UNHEX('E4B8AD'), UNHEX('E69687');

+-----------------+-----------------+
| UNHEX('E4B8AD') | UNHEX('E69687') |
+-----------------+-----------------+
| 中              | 文              |
+-----------------+-----------------+

7. Hexadecimal encoding-decoding cycle verification

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

+---------------------+------------------------+
| UNHEX(HEX('Hello')) | UNHEX(HEX('Test123'))  |
+---------------------+------------------------+
| Hello               | Test123                |
+---------------------+------------------------+

Keywords

UNHEX, UNHEX_NULL, HEX