MariaDB HEX() Function

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

The UNHEX() function is the inverse of the HEX() function.

MariaDB HEX() Syntax

Here is the syntax of the MariaDB HEX() function:

HEX(number)
HEX(string)

Parameters

number/string

Required. Requires a number or string represented in hexadecimal.

If you do not provide a parameter, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'HEX'.

Return value

The MariaDB HEX() function returns a string representing the hexadecimal value of the given number or string.

If the argument is a number, the HEX() function returns the hexadecimal value of that number.

If the argument is a string, the HEX() function converts each character to 2 hexadecimal characters.

If the argument is NULL, the HEX() function will return NULL.

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

MariaDB HEX() Examples

Basic usage

This statement shows the basic usage of the MariaDB HEX() function:

SELECT
    HEX(16),
    HEX(255),
    HEX('a'),
    HEX('b'),
    HEX('c'),
    HEX('abc'),
    HEX(NULL)\G

Output:

*************************** 1\. row ***************************
   HEX(16): 10
  HEX(255): FF
  HEX('a'): 61
  HEX('b'): 62
  HEX('c'): 63
HEX('abc'): 616263
 HEX(NULL): NULL

Number Parameters

This statement returns the hexadecimal representation of numbers using the MariaDB HEX() function.

SELECT
    HEX(0),
    HEX(1),
    HEX(2),
    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(28),
    HEX(29),
    HEX(30),
    HEX(31),
    HEX(32),
    HEX(33)\G

Output:

*************************** 1\. row ***************************
 HEX(0): 0
 HEX(1): 1
 HEX(2): 2
 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(28): 1C
HEX(29): 1D
HEX(30): 1E
HEX(31): 1F
HEX(32): 20
HEX(33): 21
1 row in set (0.000 sec)

String Parameters

This statement returns the hexadecimal representation of some character or string using the MariaDB HEX() function.

SELECT
    HEX('a'),
    HEX('A'),
    HEX('abc');

Output:

+----------+----------+------------+
| HEX('a') | HEX('A') | HEX('abc') |
+----------+----------+------------+
| 61       | 41       | 616263     |
+----------+----------+------------+

Internet6

As of MariaDB 10.5.0, HEX() with INET6 parameters returns the hexadecimal representation of the underlying 16-byte binary string.

SELECT HEX(CAST('2403:A200:A200:1100:0000:0000:0F00:0003' AS INET6));

Output:

+---------------------------------------------------------------+
| HEX(CAST('2403:A200:A200:1100:0000:0000:0F00:0003' AS INET6)) |
+---------------------------------------------------------------+
| 2403A200A2001100000000000F000003                              |
+---------------------------------------------------------------+

Here is anther example, but using the same short form for the above IPv6 address:

SELECT HEX(CAST('2403:A200:A200:1100:0000:0000:F00:3' AS INET6));

Output:

+-----------------------------------------------------------+
| HEX(CAST('2403:A200:A200:1100:0000:0000:F00:3' AS INET6)) |
+-----------------------------------------------------------+
| 2403A200A2001100000000000F000003                          |
+-----------------------------------------------------------+

Conclusion

The MariaDB HEX() function returns the string representation of the hexadecimal value of a given number or string.