UNHEX(str)

MySQL 8.4

For a string argument str, UNHEX(str) 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

MYSQL

SELECT UNHEX('4D7953514C');
'MySQL'
SELECT X'4D7953514C';
'MySQL'
SELECT UNHEX(HEX('string'));
'string'
SELECT HEX(UNHEX('1267'));
'1267'

Examples

Example

The characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If the argument contains any nonhexadecimal digits, or is itself NULL, the result is NULL:

MYSQL

SELECT UNHEX('4D7953514C');
'MySQL'
SELECT X'4D7953514C';
'MySQL'
SELECT UNHEX(HEX('string'));
'string'
SELECT HEX(UNHEX('1267'));
'1267'

Example 2

A NULL result can also occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. For example, '41' is stored into a CHAR(3) column as '41 ' and retrieved as '41' (with the trailing pad space stripped), so UNHEX() for the column value returns X'41'. By contrast, '41' is stored into a BINARY(3) column as '41\0' and retrieved as '41\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so UNHEX() for the column value returns NULL.

MYSQL

SELECT UNHEX('GG');
+-------------+
| UNHEX('GG') |
+-------------+
| NULL        |
+-------------+

SELECT UNHEX(NULL);
+-------------+
| UNHEX(NULL) |
+-------------+
| NULL        |
+-------------+