MySQL | Hexadecimal Literals
It can be defined as the value given to variables or constants.
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
int f = 2
Here f is the variable and 2 is the numeric literal.
These are the whole numbers whose base is 16 (in numbering system).
int y = X'12A'
A hexadecimal literal should be preceded by 0x or X, its value is 12A, i.e., equivalent to 298 in the decimal number system.
Hexadecimal literal values are written in following form:
X'val' x'val' 0xval
val represent any hexadecimal number, i.e., any digit in range (0, 1, …..D, E, F). In 0xval notation 0x is case sensitive, so it can’t be written as 0X’val’. However, lettercase of the digits leading X or 0x is case insensitive.
These are valid literals.
X'015' X'01aF' x'BC' x'2d' 0x7e4 0x88bA
These are invalid literals.
X'1s' // s is not in range (0, 1, ...A, .., F) 0X'1' // OX is not a valid notation, // correct notation is 0x
Note: The notation of form X’val’ or x’val’ contain even no of digits otherwise a syntax error will be raised.
ERROR 1064: You have an error in your in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘X’3”.
By padding zero at beginning of the string, the error can be avoided.
mysql> select X'03' ->♥
If values written in form 0xval contain an odd no of digits, then it is treated as containing an extra leading zero at starting of the value. Like, 0x123 is treated as 0x0123.
By default, hexadecimal literal is a binary string where each pair represents a character.
mysql>select 0x65; -> e mysql>select 5461626c65; -> Table mysql>select 0x465; -> ♦e
Here, in the last literal 0x465, the output is ‘♦e’ because 65 represents letter ‘e’ and 4 represent ♦.
An empty hexadecimal literal value (X’ ‘) is a zero-length binary string.
mysql>select X'', length(X'') -> binary 0
Operations on hexadecimal literals –
- COLLATE operation:
The default, character set of hexadecimal literal can be changed by using an optional character set introducer and COLLATE clause to convert it into a string of particular character set and collation.
[_charset_name] literal [COLLATE collation_name]
mysql>select _utf8 X'4745454B' -> GEEK mysql>select _utf8 X'4745454B' COLLATE utf8_danish_ci; -> GEEK
- BIT Operations;
mysql>select X'01' | X'02', hex(X'01' | X'02') -> 3 3 mysql>select _binary X'01' | X'02', hex(_binary X'01' | X'02') -> ♥ 03
For hexadecimal literals, bit operations result in a BIGINT value. As in the above code, without using _binary introducer the most significant bit containing zero is not displayed. So, by using _binary introducer we can specify them explicitly in the binary context.
- Arithmetic Operations:
In numeric context, hexadecimal literal are treated as BIGINT (64 bit integer). For numerical context, we can use arithmetic operators like +, -, *, /, %;
mysql>select 0x67+0 -> 103 mysql>select 0x45*2 -> 138
- HEX() / UNHEX() fun:
We can use in-built function HEX() to convert a string or number in hexadecimal string.
mysql>select hex('e') -> 65 mysql>select hex('table') -> 5461626C65
Similarly, UNHEX() function is used to convert each pair of hexadecimal digit into corresponding character.
mysql>select unhex('5645') ->VE mysql>select unhex('qq') ->NULL
In the last query we are unhexing the non-hexadecimal digits i.e (‘qq’) which return NULL.