SQL Tutorial SQL Advanced SQL Database SQL References

MySQL UNHEX() Function



The MySQL 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

str Required. Specify the hexadecimal string to be converted into binary string.

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.

mysql> SELECT UNHEX('4D7953514C');
Result: 'MySQL'

mysql> SELECT X'4D7953514C';
Result: 'MySQL'

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

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

mysql> SELECT UNHEX('GG');
Result: NULL

Example 2:

Consider a database table called Sample with the following records:

Datastr
Data 14C6561726E696E67
Data 24D7953514C
Data 36973
Data 446756E
Data 5212E

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:

DatastrUNHEX_Value
Data 14C6561726E696E67Learning
Data 24D7953514CMySQL
Data 36973is
Data 446756EFun
Data 5212E!.

❮ MySQL Functions