MySQL UNHEX() Function

In MySQL, the UNHEX() function converts a string representing a hexadecimal value to bytes and returns the corresponding binary string.

The UNHEX() function interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number.

The UNHEX() function is the inverse function of HEX().

UNHEX() Syntax

Here is the syntax of MySQL UNHEX() function:

UNHEX(string)

Parameters

string
Required. Hexadecimal numeric string. Only hexadecimal symbols (0...9, A...F, a...f) are allowed in the string.

Return value

The UNHEX() function returns a binary string.

  • If the argument string is not a valid hexadecimal value, the UNHEX() function will return NULL.
  • The UNHEX() function will return NULL if the argument is NULL.

Since the UNHEX() function returns a binary string, the result displayed on the mysql client may be hexadecimal. Please use --binary-as-hex option to disable displaying binary content as hex.

UNHEX() Examples

Here are some examples of MySQL UNHEX() function.

SELECT
    HEX('Hello'),
    UNHEX(HEX('Hello')),
    UNHEX('Invalid'),
    UNHEX(NULL)\G

       HEX('Hello'): 48656C6C6F
UNHEX(HEX('Hello')): Hello
   UNHEX('Invalid'): NULL
        UNHEX(NULL): NULL

If we didn’t disable --binary-as-hex, the output will be:

*************************** 1. row ***************************
       HEX('Hello'): 48656C6C6F
UNHEX(HEX('Hello')): 0x48656C6C6F
   UNHEX('Invalid'): NULL
        UNHEX(NULL): NULL