How the HEX() function works in Mariadb?

The HEX() function is a string function that returns the hexadecimal representation of a numeric or string value.

Posted on

The HEX() function is a string function that returns the hexadecimal representation of a numeric or string value. It can be used to convert a value into a hexadecimal format for various purposes, such as encryption, hashing, or encoding. The HEX() function is compatible with the SQL standard and supports various data types, such as INT, DECIMAL, VARCHAR, DATE, TIME, and DATETIME.

Syntax

The syntax of the HEX() function is as follows:

HEX(value)

where value is the value to be converted into hexadecimal. The function returns a string that contains the hexadecimal representation of the value. If the value is NULL, the function returns NULL. If the value is a numeric value, the function returns the hexadecimal representation of the value as an unsigned integer. If the value is a string value, the function returns the hexadecimal representation of each character in the string.

Examples

Example 1: Convert a numeric value into hexadecimal

The following example uses the HEX() function to convert a numeric value into hexadecimal.

SELECT HEX(255) AS hex;

The function returns a string that contains the hexadecimal representation of the numeric value as an unsigned integer. The output is:

+------+
| hex  |
+------+
| FF   |
+------+

The hexadecimal representation of 255 is FF.

Example 2: Convert a string value into hexadecimal

The following example uses the HEX() function to convert a string value into hexadecimal.

SELECT HEX('Hello') AS hex;

The function returns a string that contains the hexadecimal representation of each character in the string. The output is:

+------------+
| hex        |
+------------+
| 48656C6C6F |
+------------+

The hexadecimal representation of ‘Hello’ is 48656C6C6F.

Example 3: Convert a date value into hexadecimal

The following example uses the HEX() function to convert a date value into hexadecimal.

SELECT HEX('2024-02-08') AS hex;

The function returns a string that contains the hexadecimal representation of the date value as an unsigned integer. The output is:

+----------------------+
| hex                  |
+----------------------+
| 323032342D30322D3038 |
+----------------------+

The hexadecimal representation of ‘2024-02-08’ is 323032342D30322D3038.

Example 4: Convert a time value into hexadecimal

The following example uses the HEX() function to convert a time value into hexadecimal.

SELECT HEX('09:05:39') AS hex;

The function returns a string that contains the hexadecimal representation of the time value as an unsigned integer. The output is:

+------------------+
| hex              |
+------------------+
| 30393A30353A3339 |
+------------------+

The hexadecimal representation of ‘09:05:39’ is 30393A30353A3339.

Example 5: Convert a datetime value into hexadecimal

The following example uses the HEX() function to convert a datetime value into hexadecimal.

SELECT HEX('2024-02-08 09:05:39') AS hex;

The function returns a string that contains the hexadecimal representation of the datetime value as an unsigned integer. The output is:

+----------------------------------------+
| hex                                    |
+----------------------------------------+
| 323032342D30322D30382030393A30353A3339 |
+----------------------------------------+

The hexadecimal representation of ‘2024-02-08 09:05:39’ is 323032342D30322D30382030393A30353A3339.

Some of the functions that are related to the HEX() function are:

  • UNHEX(): This function is the inverse of the HEX() function. It returns the original value of a hexadecimal string. It has the same syntax as the HEX() function. For example, UNHEX('FF') returns 255, while UNHEX('48656C6C6F') returns ‘Hello’.
  • BIN(): This function returns the binary representation of a numeric value. It has a different syntax and behavior from the HEX() function. For example, BIN(255) returns ‘11111111’, while HEX(255) returns ‘FF’.
  • CONV(): This function converts a value from one numeric base to another. It has a different syntax and behavior from the HEX() function. For example, CONV(255, 10, 16) returns ‘FF’, while HEX(255) returns ‘FF’.

Conclusion

The HEX() function is a useful function to convert a value into a hexadecimal format. It supports various data types and follows the SQL standard. It can be used to convert a numeric, string, date, or time value into a hexadecimal string. It has some related functions that have similar or different functionalities. The HEX() function is a powerful tool for data conversion and manipulation in Mariadb.