HEX
Description
The HEX function converts input parameters to hexadecimal string representation. This function is MySQL-compatible and supports both numeric and string input types with different conversion rules.
If the input parameter is a number (BIGINT type), returns the hexadecimal string representation of that number.
If the input parameter is a string, converts each character (by byte) to two hexadecimal characters, then concatenates all converted characters into the result string.
Syntax
HEX(<expr>)
Parameters
| Parameter | Description |
|---|---|
<expr> | Input parameter, can be BIGINT type number or VARCHAR type string |
Return Value
Returns VARCHAR type, representing the hexadecimal representation of the input parameter.
Conversion rules:
- Numeric input: Converts to corresponding hexadecimal value (within BIGINT range)
- String input: Each byte converts to two uppercase hexadecimal characters
- Negative numbers are converted in two's complement binary form
Special cases:
- If parameter is NULL, returns NULL
- Number 0 converts to '0'
- Empty string converts to empty string
- Negative numbers convert to hexadecimal representation of 64-bit two's complement
Examples
- Basic number conversion
SELECT HEX(12), HEX(-1);
+---------+------------------+
| HEX(12) | HEX(-1) |
+---------+------------------+
| C | FFFFFFFFFFFFFFFF |
+---------+------------------+
- String conversion
SELECT HEX('1'), HEX('@'), HEX('12');
+----------+----------+-----------+
| HEX('1') | HEX('@') | HEX('12') |
+----------+----------+-----------+
| 31 | 40 | 3132 |
+----------+----------+-----------+
- Large integer conversion
SELECT HEX(255), HEX(65535), HEX(16777215);
+----------+------------+----------------+
| HEX(255) | HEX(65535) | HEX(16777215) |
+----------+------------+----------------+
| FF | FFFF | FFFFFF |
+----------+------------+----------------+
- NULL value handling
SELECT HEX(NULL);
+-----------+
| HEX(NULL) |
+-----------+
| NULL |
+-----------+
- Zero and empty string
SELECT HEX(0), HEX('');
+--------+--------+
| HEX(0) | HEX('') |
+--------+--------+
| 0 | |
+--------+--------+
- Whitespace characters (each byte → two hex digits)
SELECT HEX(' '), HEX('\t'), HEX('\n');
+----------+-----------+-----------+
| HEX(' ') | HEX('\t') | HEX('\n') |
+----------+-----------+-----------+
| 20 | 09 | 0A |
+----------+-----------+-----------+
- UTF-8 multi-byte strings
SELECT HEX('ṭṛì'), HEX('ḍḍumai');
+------------------+----------------------+
| HEX('ṭṛì') | HEX('ḍḍumai') |
+------------------+----------------------+
| E1B9ADE1B99BC3AC | E1B88DE1B88D756D6169 |
+------------------+----------------------+
- Negative integers (64-bit two's complement)
SELECT HEX(-128), HEX(-32768);
+------------------+------------------+
| HEX(-128) | HEX(-32768) |
+------------------+------------------+
| FFFFFFFFFFFFFF80 | FFFFFFFFFFFF8000 |
+------------------+------------------+
- Mixed alphanumeric strings
SELECT HEX('A1'), HEX('Hello!');
+-----------+---------------+
| HEX('A1') | HEX('Hello!') |
+-----------+---------------+
| 4131 | 48656C6C6F21 |
+-----------+---------------+
