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 anINET6
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'