Skip navigation links
8.1.5 Boolean Values »
Section Navigation      [Toggle]

8.1.4. Hexadecimal Values

MySQL supports hexadecimal values, written using X'val', x'val', or 0xval format, where val contains hexadecimal digits (0..9, A..F). Lettercase of the digits does not matter. For values written using X'val' or x'val' format, val must contain an even number of digits. For values written using 0xval syntax, values that contain an odd number of digits are treated as having an extra leading 0. For example, 0x0a and 0xaaa are interpreted as 0x0a and 0x0aaa.

In numeric contexts, hexadecimal values act like integers (64-bit precision). In string contexts, they act like binary strings, where each pair of hex digits is converted to a character: 

mysql> SELECT X'4D7953514C';
        -> 'MySQL'
mysql> SELECT 0x0a+0;
        -> 10
mysql> SELECT 0x5061756c;
        -> 'Paul'

The default type of a hexadecimal value is a string. If you want to ensure that the value is treated as a number, you can use CAST(... AS UNSIGNED): 

mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
        -> 'A', 65

The X'hexstring' syntax is based on standard SQL. The 0x syntax is based on ODBC. Hexadecimal strings are often used by ODBC to supply values for BLOB columns.

You can convert a string or a number to a string in hexadecimal format with the HEX() function: 

mysql> SELECT HEX('cat');
        -> '636174'
mysql> SELECT 0x636174;
        -> 'cat'

User Comments

Posted by Case Larsen on May 17 2002 2:24am[Delete] [Edit]

Why does "select * from t where md5 like
0xad65.....;" return rows while "select * from t
where md5 = 0xad65...;" not return rows?

The table 't' was declared
create table t (md5 char(16))
and row was inserted as
insert into t (md5) values (0xad65....);

Posted by Frank Solensky on November 21 2002 7:21am[Delete] [Edit]

If you've got the iso-8859-15 (aka 'Latin-9')
character set installed, 0xA4. I assume that
you'd have to recompile MySQL for UTF character
sets. [21-Nov-2002]

Posted by Mitch Bechtel on July 23 2003 1:34pm[Delete] [Edit]

To compare a column to a hex value, cast the hex value to the column type. For example, to compare a column of type BINARY(8) with the hex value of 0xa0f44ef7a52411de, you could use:

SELECT * FROM SomeTable WHERE BinaryColumn = CAST( x'a0f44ef7a52411de' AS BINARY )

OR (see first comment posted by Case Larsen)

SELECT * FROM t WHERE md5 = CAST( 0xad65... AS CHAR )

Posted by Laurent Quérel on October 9 2004 11:53am[Delete] [Edit]

Warning x'00...' is converted in null value.

select
x'000bdddc0e9153f5a93447fc3310f710',
x'0bdddc0e9153f5a93447fc3310f710'

first column is null, second column is correct.

Posted by Carlton Dickson on September 16 2005 12:35pm[Delete] [Edit]

As of release 4.1.2 (28 May 2004) MySQL implemented the UNHEX() function which undoes HEX() obviously...thought I would mention as the HEX documentation doesn't specify this and I needed a method of UNHEX'ing a value from a column

I used the two with AES_ENCRYPT/AES_DECRYPT to store and retrieve hex strings once they had been encrypted/decrypted

HEX(AES_ENCRYPT(columnName, 'password'));

AES_DECRYPT(UNHEX(columnName), 'password');

Posted by kamiar kanani on January 2 2007 12:42pm[Delete] [Edit]

If you use MD5, it returns to you a 32-bit char string
(for example):
>SELECT MD5(a) FROM t WHERE cond;
c2268fbe786c2718cd5c4d26ce09cb26
If you want to get 16-bit binary of MD5 output you can type:
>SELECT UNHEX(MD5(a)) FROM t WHERE cond;

+-----------------------------+
| unhex(md5(''))              |
+-----------------------------+
| �&懞l'�\M&�  �&               |
+-----------------------------+
1 row in set (0.00 sec)

it is 16-byte output
it looks strage because it contain special characters

Add your own comment.