MySQL UNHEX() Function

---

---

The MySQL UNHEX() function is used to convert a hexadecimal string representation to its original binary data form.

The UNHEX() function can be useful in scenarios such as decoding data that was encoded using the HEX() function, converting hexadecimal values back to their original binary format, etc.

Syntax

Following is the syntax of MySQL UNHEX() function −

UNHEX(val);

Parameters

This function accepts a hexadecimal string as a parameter.

Return Value

This function returns the binary representation as a string of the given hexadecimal value.

Example

In the following example, we are converting the hexadecimal string representation "4D" to its original binary data form −

SELECT UNHEX('4D');

Following is the output of the above code −

UNHEX('4D')
0x4D

If you disable the --binary-as-hex value, the result of the above query will be as follows −

SELECT UNHEX('4D');

The result produced is as follows −

UNHEX('4D')
M

Example

If you pass a non-hexadecimal value as an argument to UNHEX(), it returns NULL −

SELECT UNHEX('non-hexadecimal-string');

The output obtained is as follows −

UNHEX('non-hexadecimal-string')
NULL

Example

In the following example, we are performing the UNHEX operation on the HEX value of "test123". It converts the hexadecimal representation of "test123" back to its original form −

SELECT UNHEX(HEX('test123'));

We get the output as follows −

UNHEX(HEX('test123'))
test123

Example

You can also convert the values of a columns from hexa-decimal from binary using the UNHEX() function.

Let us create a table named "EMP" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE EMP(
   NAME  CHAR(20) NOT NULL,
   AGE INT,
   INCOME FLOAT
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO EMP VALUES 
('52616a75', 19, 2000),
('5261686d616e', 20, 7000),
('526f62657274', 25, 5000);

The EMP obtained is as follows −

NAME	AGE	INCOME
52616a75	19	2000
5261686d616e	20	7000
526f62657274	25	5000

Following query converts the values of the NAME column from hexa-decimal string to binary −

SELECT NAME, AGE, INCOME UNHEX(NAME) 
FROM EMP;

After executing the above code, we get the following output −

NAME	AGE	INCOME	UNHEX(NAME)
52616a75	19	2000	Raju
5261686d616e	20	7000	Rahman
526f62657274	25	5000	Robert