6.1.1.3 Hexadecimal Values

MySQL supports hexadecimal values. In numeric context these act like an integer (64-bit precision). In string context these act like a binary string where each pair of hex digits is converted to a character:

mysql> SELECT x'4D7953514C';
         -> MySQL
mysql> SELECT 0xa+0;
         -> 10
mysql> SELECT 0x5061756c;
         -> Paul

In MySQL 4.1 (and in MySQL 4.0 when using the --new option) the default type of of a hexadecimal value is a string. If you want to be sure that the string is threated as a number, you can use CAST( ... AS UNSIGNED) on the hexadecimal value.

The x'hexstring' syntax (new in 4.0) is based on standard SQL and 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 string in hexadecimal format with the HEX() function.

User Comments

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....);

How can I define a € (euro)? What numeric type is the best?

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]

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 )

Add your own comment.