How HEX() Works in MariaDB

by

In MariaDB, HEX() is a built-in string function that returns a hexadecimal string representation of its argument.

It works like this:

  • If its argument is a number, HEX() returns a string representation of the hexadecimal value of the number.
  • If the argument is a string, HEX() returns a hexadecimal string representation of the string, where each byte of each character in the string is converted to two hexadecimal digits.
  • Starting in MariaDB 10.5.0, HEX() with an INET6 argument returns a hexadecimal representation of the underlying 16-byte binary string.

Syntax

The syntax goes like this:

HEX(N_or_S)

Where N_or_S is a number or a string.

Example

Here’s a basic example:

SELECT HEX(15);

Result:

+---------+
| HEX(15) |
+---------+
| F       |
+---------+

Here, we got the hexadecimal value of 15, which is F.

In decimal, the counting would go like this: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15.

But in hexadecimal, it goes like this: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F.

So if our number had been between 0 and 9, we would have got the same value as its decimal counterpart.

Here’s another example that demonstrates how the hexadecimal system increments its values:

SELECT 
    HEX(0),
    HEX(1),
    HEX(2),
    HEX(3),
    HEX(4),
    HEX(5),
    HEX(6),
    HEX(7),
    HEX(8),
    HEX(9),
    HEX(10),
    HEX(11),
    HEX(12),
    HEX(13),
    HEX(14),
    HEX(15),
    HEX(16),
    HEX(17),
    HEX(18),
    HEX(19),
    HEX(20),
    HEX(21),
    HEX(22),
    HEX(23),
    HEX(24),
    HEX(25),
    HEX(26),
    HEX(27),
    HEX(28),
    HEX(29),
    HEX(30),
    HEX(31),
    HEX(32),
    HEX(789456768);

Result (using vertical output):

        HEX(0): 0
        HEX(1): 1
        HEX(2): 2
        HEX(3): 3
        HEX(4): 4
        HEX(5): 5
        HEX(6): 6
        HEX(7): 7
        HEX(8): 8
        HEX(9): 9
       HEX(10): A
       HEX(11): B
       HEX(12): C
       HEX(13): D
       HEX(14): E
       HEX(15): F
       HEX(16): 10
       HEX(17): 11
       HEX(18): 12
       HEX(19): 13
       HEX(20): 14
       HEX(21): 15
       HEX(22): 16
       HEX(23): 17
       HEX(24): 18
       HEX(25): 19
       HEX(26): 1A
       HEX(27): 1B
       HEX(28): 1C
       HEX(29): 1D
       HEX(30): 1E
       HEX(31): 1F
       HEX(32): 20
HEX(789456768): 2F0E2780

Strings

As mentioned, if the argument is a string, HEX() returns a hexadecimal string representation of the string, where each byte of each character in the string is converted to two hexadecimal digits.

Example:

SELECT 
    HEX('a'),
    HEX('aa'),
    HEX('A'),
    HEX('AA'),
    HEX('b'),
    HEX('bb'),
    HEX('B'),
    HEX('BB'),
    HEX('Cat'),
    HEX('Dog'),
    HEX('Paws & Claws'),
    HEX('Zebra');

Result (using vertical output): 

           HEX('a'): 61
          HEX('aa'): 6161
           HEX('A'): 41
          HEX('AA'): 4141
           HEX('b'): 62
          HEX('bb'): 6262
           HEX('B'): 42
          HEX('BB'): 4242
         HEX('Cat'): 436174
         HEX('Dog'): 446F67
HEX('Paws & Claws'): 50617773202620436C617773
       HEX('Zebra'): 5A65627261

INET6

Starting in MariaDB 10.5.0, HEX() with an INET6 argument returns a hexadecimal representation of the underlying 16-byte binary string.

Example:

SELECT HEX(CAST('2001:0db8:3c4d:0015:0000:0000:1a2f:1a2b' AS INET6));

Result:

+---------------------------------------------------------------+
| HEX(CAST('2001:0db8:3c4d:0015:0000:0000:1a2f:1a2b' AS INET6)) |
+---------------------------------------------------------------+
| 20010DB83C4D0015000000001A2F1A2B                              |
+---------------------------------------------------------------+

Here it is again, but using the abbreviated form of the same IPv6 address:

SELECT HEX(CAST('2001:db8:3c4d:15::1a2f:1a2b' AS INET6));

Result:

+---------------------------------------------------+
| HEX(CAST('2001:db8:3c4d:15::1a2f:1a2b' AS INET6)) |
+---------------------------------------------------+
| 20010DB83C4D0015000000001A2F1A2B                  |
+---------------------------------------------------+

Null Arguments

Passing null to HEX() results in null:

SELECT HEX(null);

Result:

+-----------+
| HEX(null) |
+-----------+
| NULL      |
+-----------+

Passing No Arguments

Calling HEX() without passing any arguments results in an error:

SELECT HEX();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'HEX'