MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL 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.




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:

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

The query 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:

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

❮ MySQL Functions