Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Hexadecimal Literals

9.1.4 Hexadecimal Literals

Hexadecimal literal values are written using X'val', x'val', or 0xval notation, 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' notation, val must contain an even number of digits or a syntax error occurs. To correct the problem, pad the value with a leading zero:

mysql> SELECT X'FFF'+0;
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near 'X'FFF'+0'
mysql> SELECT X'0FFF'+0;
| X'0FFF'+0 |
|      4095 |

For values written using 0xval notation, 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.

A hexadecimal literal may have an optional character set introducer and COLLATE clause:

[_charset_name] X'val' [COLLATE collation_name]


SELECT _binary X'4D7953514C';
SELECT _utf8 X'4D7953514C';
SELECT _utf8 X'4D7953514C' COLLATE utf8_danish_ci;

The examples use X'val' notation, but x'val' and 0xval notations permit introducers as well. For more information about introducers, see Section, “Character String Literal Character Set and Collation”.

By default, a hexadecimal literal is a binary string, where each pair of hexadecimal digits represents a character:

mysql> SELECT X'4D7953514C';
        -> 'MySQL'
mysql> SELECT 0x5472616e73616374696f6e;
        -> 'Transaction'

In numeric contexts, MySQL treats a hexadecimal literal like a BIGINT (64-bit integer). To ensure numeric treatment of a value, add 0 or use CAST(... AS UNSIGNED):

mysql> SELECT X'41', X'41'+0, CAST(X'41' AS UNSIGNED);
        -> 'A', 65, 65

Hexadecimal literals assigned to user-defined variables are treated as binary strings. To assign a hexadecimal literal as a number to a variable, use it in numeric context:

mysql> SET @v1 = X'41';
mysql> SET @v2 = X'41'+0, @v3 = CAST(X'41' AS UNSIGNED);
mysql> SELECT @v1, @v2, @v3;
| @v1  | @v2  | @v3  |
| A    |   65 |   65 |

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

To convert a string or a number to a string in hexadecimal format, use the HEX() function:

mysql> SELECT HEX('cat');
        -> '636174'
mysql> SELECT X'636174';
        -> 'cat'

User Comments
Sign Up Login You must be logged in to post a comment.