Open In App

MySQL | Hexadecimal Literals

Improve
Improve
Like Article
Like
Save
Share
Report

Literal:
It can be defined as the value given to variables or constants.

Example:

int f = 2 

Here f is the variable and 2 is the numeric literal.

Hexadecimal literal:
These are the whole numbers whose base is 16 (in numbering system).

Example:

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.

Syntax:
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.

Examples:
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.

mysql>select X'3' 

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.

Default representation:
By default, hexadecimal literal is a binary string where each pair represents a character.

Example:

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 –

  1. 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.

    Syntax:

    [_charset_name] literal [COLLATE collation_name]

    Example:

    mysql>select _utf8 X'4745454B'
         -> GEEK
    mysql>select _utf8 X'4745454B' COLLATE utf8_danish_ci;
         -> GEEK
    
  2. 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.

  3. 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
    
  4. 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.


Last Updated : 25 Nov, 2019
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads