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

1. Basic number conversion

SELECT HEX(12), HEX(-1);

+---------+------------------+
| HEX(12) | HEX(-1)          |
+---------+------------------+
| C       | FFFFFFFFFFFFFFFF |
+---------+------------------+

2. String conversion

SELECT HEX('1'), HEX('@'), HEX('12');

+----------+----------+-----------+
| HEX('1') | HEX('@') | HEX('12') |
+----------+----------+-----------+
| 31       | 40       | 3132      |
+----------+----------+-----------+

3. Large integer conversion

SELECT HEX(255), HEX(65535), HEX(16777215);

+----------+------------+----------------+
| HEX(255) | HEX(65535) | HEX(16777215)  |
+----------+------------+----------------+
| FF       | FFFF       | FFFFFF         |
+----------+------------+----------------+

4. NULL value handling

SELECT HEX(NULL);

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

5. Zero and empty string

SELECT HEX(0), HEX('');

+--------+--------+
| HEX(0) | HEX('') |
+--------+--------+
| 0      |        |
+--------+--------+

6. Whitespace characters (each byte → two hex digits)

SELECT HEX(' '), HEX('\t'), HEX('\n');

+----------+-----------+-----------+
| HEX(' ') | HEX('\t') | HEX('\n') |
+----------+-----------+-----------+
| 20       | 09        | 0A        |
+----------+-----------+-----------+

7. UTF-8 multi-byte strings

SELECT HEX('ṭṛì'), HEX('ḍḍumai');

+------------------+----------------------+
| HEX('ṭṛì')       | HEX('ḍḍumai')        |
+------------------+----------------------+
| E1B9ADE1B99BC3AC | E1B88DE1B88D756D6169 |
+------------------+----------------------+

8. Negative integers (64-bit two's complement)

SELECT HEX(-128), HEX(-32768);

+------------------+------------------+
| HEX(-128)        | HEX(-32768)      |
+------------------+------------------+
| FFFFFFFFFFFFFF80 | FFFFFFFFFFFF8000 |
+------------------+------------------+

9. Mixed alphanumeric strings

SELECT HEX('A1'), HEX('Hello!');

+-----------+---------------+
| HEX('A1') | HEX('Hello!') |
+-----------+---------------+
| 4131      | 48656C6C6F21  |
+-----------+---------------+