Converts a number or string to its hexadecimal representation. HEX is a MaxCompute V2.0 mathematical function.
Syntax
string hex(<number>)Parameters
number: Required. Supported types: BIGINT, INT, SMALLINT, TINYINT, DOUBLE, FLOAT, DECIMAL, STRING.
Integer types (BIGINT, INT, SMALLINT, TINYINT): the function returns the hexadecimal representation of the numeric value.
Floating-point and decimal types (DOUBLE, FLOAT, DECIMAL): the function encodes the decimal string representation of the value, not the IEEE 754 bit pattern.
STRING: the function encodes the input byte-by-byte.
Return value
Returns a STRING value.
| Input | Return value |
|---|---|
| Any non-zero, non-null value | Hexadecimal string |
0 | 0 |
null | null |
Sample data
This section provides sample source data and examples for you to understand how to use the function. A table named mf_math_fun_t is created and data is inserted into the table. Sample statements:
create table if not exists mf_math_fun_t(
int_data int,
bigint_data bigint,
double_data double,
decimal_data decimal,
float_data float,
string_data string
);
insert into mf_math_fun_t values
(null, -10, 0.525, 0.525BD, cast(0.525 as float), '10'),
(-20, null, -0.1, -0.1BD, cast(-0.1 as float), '-10'),
(0, -1, null, 20.45BD, cast(-1 as float), '30'),
(-40, 4, 0.89, null, cast(0.89 as float), '-30'),
(5, -50, -1, -1BD, null, '50'),
(-60, 6, 1.5, 1.5BD, cast(1.5 as float), '-50'),
(-1, -70, -7.5, -7.5BD, cast(-7.5 as float),null ),
(-80, 1, -10.2, -10.2BD, cast(-10.2 as float), '-1' ),
(9, -90, 2.58, 2.58BD, cast(2.58 as float), '0'),
(-100, 10, -5.8, -5.8BD, cast(-5.8 as float), '-90');Query data from the mf_math_fun_t table:
select * from mf_math_fun_t;
-- The following result is returned:
+------------+-------------+-------------+--------------+------------+-------------+
| int_data | bigint_data | double_data | decimal_data | float_data | string_data |
+------------+-------------+-------------+--------------+------------+-------------+
| NULL | -10 | 0.525 | 0.525 | 0.525 | 10 |
| -20 | NULL | -0.1 | -0.1 | -0.1 | -10 |
| 0 | -1 | NULL | 20.45 | -1.0 | 30 |
| -40 | 4 | 0.89 | NULL | 0.89 | -30 |
| 5 | -50 | -1.0 | -1 | NULL | 50 |
| -60 | 6 | 1.5 | 1.5 | 1.5 | -50 |
| -1 | -70 | -7.5 | -7.5 | -7.5 | NULL |
| -80 | 1 | -10.2 | -10.2 | -10.2 | -1 |
| 9 | -90 | 2.58 | 2.58 | 2.58 | 0 |
| -100 | 10 | -5.8 | -5.8 | -5.8 | -90 |
+------------+-------------+-------------+--------------+------------+-------------+Examples
Static values
-- Integer input: returns the hex representation of the numeric value
select hex(17);
-- Returns: 11
select hex(0);
-- Returns: 0
-- String input: returns the hex encoding of the UTF-8 bytes
select hex('abc');
-- Returns: 616263
select hex('17');
-- Returns: 3137
-- Null input returns an error
select hex(null);
-- An error is returned.Integer and string inputs produce different results.hex(17)converts the number 17 to hex (11), whilehex('17')encodes the string characters'1'and'7'as bytes (3137).
Table data
The following example converts all numeric columns to hexadecimal using the mf_math_fun_t sample table.
Enable the MaxCompute V2.0 data type edition before running this query.
set odps.sql.type.system.odps2=true;
select
hex(int_data) as int_new,
hex(bigint_data) as bigint_new,
hex(double_data) as double_new,
hex(decimal_data) as decimal_new,
hex(float_data) as float_new,
hex(string_data) as string_new
from mf_math_fun_t;Result:
+------------------+------------------+------------+-------------+------------+------------+
| int_new | bigint_new | double_new | decimal_new | float_new | string_new |
+------------------+------------------+------------+-------------+------------+------------+
| NULL | FFFFFFFFFFFFFFF6 | 302E353235 | 302E353235 | 302E353235 | 3130 |
| FFFFFFFFFFFFFFEC | NULL | 2D302E31 | 2D302E31 | 2D302E31 | 2D3130 |
| 0 | FFFFFFFFFFFFFFFF | NULL | 32302E3435 | 2D31 | 3330 |
| FFFFFFFFFFFFFFD8 | 4 | 302E3839 | NULL | 302E3839 | 2D3330 |
| 5 | FFFFFFFFFFFFFFCE | 2D312E30 | 2D31 | NULL | 3530 |
| FFFFFFFFFFFFFFC4 | 6 | 312E35 | 312E35 | 312E35 | 2D3530 |
| FFFFFFFFFFFFFFFF | FFFFFFFFFFFFFFBA | 2D372E35 | 2D372E35 | 2D372E35 | NULL |
| FFFFFFFFFFFFFFB0 | 1 | 2D31302E32 | 2D31302E32 | 2D31302E32 | 2D31 |
| 9 | FFFFFFFFFFFFFFA6 | 322E3538 | 322E3538 | 322E3538 | 30 |
| FFFFFFFFFFFFFF9C | A | 2D352E38 | 2D352E38 | 2D352E38 | 2D3930 |
+------------------+------------------+------------+-------------+------------+------------+Negative integers (such as -10 stored as BIGINT) return their two's complement hexadecimal representation (FFFFFFFFFFFFFFF6). DOUBLE, FLOAT, and DECIMAL values are encoded as the hex of their decimal string representation, not as IEEE 754 bit patterns.
Related functions
HEX is a mathematical function. For related functions, see Mathematical functions.