Hexadecimal literals can be written using any of the following syntaxes:

* x'value'
* X'value' (SQL standard)
* 0xvalue (ODBC)

value is a sequence of hexadecimal digits (from 0 to 9 and from A to F). The
case of the digits does not matter. With the first two syntaxes, value must
consist of an even number of digits. With the last syntax, digits can be even,
and they are treated as if they had an extra 0 at the beginning.

Normally, hexadecimal literals are interpreted as binary string, where each
pair of digits represents a character. When used in a numeric context, they
are interpreted as integers. (See the example below). In no case can a
hexadecimal literal be a decimal number.

The first two syntaxes; X'value' and x'value, follow the SQL standard, and
behave as a string in all contexts in MariaDB since MariaDB 10.0.3 and MariaDB
5.5.31 (fixing MDEV-4489). The latter syntax, 0xvalue, is a MySQL/MariaDB
extension for hex hybrids and behaves as a string or as a number depending on
context. MySQL treats all syntaxes the same, so there may be different results
in MariaDB and MySQL (see below).

Examples
--------

Representing the a character with the three syntaxes explained above:

SELECT x'61', X'61', 0x61;
+-------+-------+------+
| x'61' | X'61' | 0x61 |
+-------+-------+------+
| a     | a     | a    |
+-------+-------+------+

Hexadecimal literals in a numeric context:

SELECT 0 + 0xF, -0xF;
+---------+------+
| 0 + 0xF | -0xF |
+---------+------+
|      15 |  -15 |
+---------+------+

Fun with Types
--------------

CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (0x31, 0x61),(COALESCE(0x31), COALESCE(0x61));

SELECT * FROM t1;
+------+------+
| a    | b    |
+------+------+
|   49 | a    |
|    1 | a    |
+------+------+

The reason for the differing results above is that when 0x31 is inserted
directly to the column, it's treated as a number, while when 0x31 is passed to
COALESCE(), it's treated as a string, because:

* HEX values have a string data type by default.
* COALESCE() has the same data type as the argument.

Differences Between MariaDB and MySQL
-------------------------------------

SELECT x'0a'+0;
+---------+
| x'0a'+0 |
+---------+
|       0 |
+---------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: '\x0A'

SELECT X'0a'+0;
+---------+
| X'0a'+0 |
+---------+
|       0 |
+---------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: '\x0A'

SELECT 0x0a+0;
+--------+
| 0x0a+0 |
+--------+
|     10 |
+--------+

In MySQL (up until at least MySQL 8.0.26):

SELECT x'0a'+0;
+---------+
| x'0a'+0 |
+---------+
|      10 |
+---------+

SELECT X'0a'+0;
+---------+
| X'0a'+0 |
+---------+
|      10 |
+---------+

SELECT 0x0a+0;
+--------+
| 0x0a+0 |
+--------+
|     10 |
+--------+

URL: https://mariadb.com/kb/en/hexadecimal-literals/