MariaDB UNHEX() Function

The MariaDB UNHEX() function returns a string containing hex representation of a number. For a string argument str, this 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 will be a binary string.

The characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If the argument contains any non-hexadecimal digits, the result will be NULL.

For a numeric argument N, the inverse of HEX(N) is not performed by UNHEX(). The inverse of HEX(N) can be performed by using the CONV() function with CONV(UNHEX(N), 16, 10) syntax.

Syntax

UNHEX(str)

Parameters

Return Value

Returns the binary string. If non-hexadecimal digits are provided this function returns NULL.

Example 1:

The example below shows the usage of UNHEX() function.

SELECT UNHEX('4D617269614442');
Result: 'MariaDB'

SELECT X'4D617269614442';
Result: 'MariaDB'

SELECT UNHEX(HEX('string'));
Result: 'string'

SELECT HEX(UNHEX('1267'));
Result: '1267'

SELECT UNHEX('GG');
Result: NULL

Example 2:

Consider a database table called Sample with the following records:

The statement given below can be used to convert the hexadecimal string of column str.

SELECT *, UNHEX(str) AS UNHEX_Value FROM Sample;

This will produce the result as shown below: