How to use hex and unhex in mysql

MysqlHow-ToBeginner · 3 min read

How to Use HEX and UNHEX Functions in MySQL

In MySQL, use HEX() to convert a string or number to its hexadecimal representation, and UNHEX() to convert a hexadecimal string back to its original form. These functions help store or display binary data as readable hex strings and reverse the process.

📐

Syntax

The HEX() function takes a string or number and returns a hexadecimal string. The UNHEX() function takes a hexadecimal string and returns the original string or binary data.

HEX(str_or_num): Converts input to hex string.
UNHEX(hex_str): Converts hex string back to original.

sql

SELECT HEX('hello') AS hex_value, UNHEX('68656C6C6F') AS original_value;

Output

+-----------+----------------+ | hex_value | original_value | +-----------+----------------+ | 68656C6C6F| hello | +-----------+----------------+

💻

Example

This example shows how to convert a text string to hexadecimal using HEX() and then convert it back using UNHEX(). It demonstrates storing and retrieving data in hex format.

sql

CREATE TEMPORARY TABLE example_data (id INT, data VARBINARY(100));

INSERT INTO example_data VALUES (1, UNHEX(HEX('MySQL')));

SELECT id, HEX(data) AS hex_data, CAST(data AS CHAR) AS original_data FROM example_data;

Output

+----+-----------+---------------+ | id | hex_data | original_data | +----+-----------+---------------+ | 1 | 4D7953514C| MySQL | +----+-----------+---------------+

⚠️

Common Pitfalls

Common mistakes include:

Passing non-hex strings to UNHEX() which returns NULL.
Using HEX() on binary data without proper casting, leading to unexpected results.
Confusing string length after conversion because hex doubles the length.

Always ensure the input to UNHEX() is a valid even-length hex string.

sql

SELECT UNHEX('123'); -- Returns NULL because length is odd

SELECT UNHEX('7A7B7C'); -- Correct usage

-- Correct way to convert binary data to hex and back
SELECT HEX(CAST('abc' AS BINARY)) AS hex_val, CAST(UNHEX(HEX(CAST('abc' AS BINARY))) AS CHAR) AS original_val;

Output

+--------------+ | UNHEX('123') | +--------------+ | NULL | +--------------+ +------------+--------------+ | hex_val | original_val | +------------+--------------+ | 616263 | abc | +------------+--------------+

📊

Quick Reference

Function	Input	Output	Description
HEX(str_or_num)	String or number	Hexadecimal string	Converts input to hex string
UNHEX(hex_str)	Hexadecimal string	Original string or binary	Converts hex string back to original
Note	Hex string length	Must be even	UNHEX requires even-length hex strings

✅

Key Takeaways

Use HEX() to convert strings or numbers to hexadecimal format in MySQL.

Use UNHEX() to convert hexadecimal strings back to their original form.

Ensure hex strings passed to UNHEX() have an even length to avoid NULL results.

HEX output length is twice the input string length because each byte becomes two hex digits.

Cast binary data properly when using HEX and UNHEX to avoid unexpected results.