Introduction to the CONV function - MaxCompute - Alibaba Cloud Documentation Center

CONV is a number system conversion function that converts a number from one number system to another and returns the result as a string.

Syntax

STRING CONV(<input>, BIGINT <from_base>, BIGINT <to_base>)

Parameters

Parameter	Required	Description
input	Yes	The input value to be converted. It must be of the STRING type. Note If the project is in Hive-compatible mode, which is enabled by using the `set odps.sql.hive.compatible=true;` command, implicit conversion from BIGINT and DOUBLE is supported. If the project is not in Hive-compatible mode, which is disabled by using the `set odps.sql.hive.compatible=false;` command, implicit conversion from BIGINT and DOUBLE is not supported.
from_base	Yes	The number system value must be a decimal integer 2, 8, 10, or 16. Implicit type conversion is not supported.
to_base	Yes	The number system value must be a decimal integer 2, 8, 10, or 16. Implicit type conversion is not supported.

Return value

A value of the STRING type is returned. The return value varies based on the following rules:

If input, from_base, or to_base is null, null is returned.
The conversion process runs at 64-bit precision. If an overflow occurs, null is returned.
If input is of the BIGINT or DOUBLE type, null is returned in non-Hive-compatible mode because implicit conversion is not supported.
If input is a negative value, null is returned in non-Hive-compatible mode. In Hive-compatible mode, 0 is returned.
If input is a decimal value, null is returned in non-Hive-compatible mode. In Hive-compatible mode, it is converted into an integer before the conversion of number systems. The decimal part is discarded.

Sample data

This section provides sample source data and examples for you to understand how to use the function. In this topic, 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. Sample statement:

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 data

--The value 12 is returned.
SELECT CONV('1100', 2, 10);
--The value C is returned.
SELECT CONV('1100', 2, 16);
--The value 171 is returned.
SELECT CONV('ab', 16, 10);
--The value AB is returned.
SELECT CONV('ab', 16, 16);
--The value NULL is returned.
SELECT CONV('1100', null, 10);

Examples: Table data

Based on the sample data, convert the data to the binary format. Sample statement:

SELECT CONV(bigint_data,10,2) AS bigint_new, CONV(double_data,10,2) AS double_new, CONV(decimal_data,10,2) AS decimal_new, CONV(string_data,10,2) AS string_new FROM mf_math_fun_t;

The following result is returned.

+------------+------------+-------------+------------+
| bigint_new | double_new | decimal_new | string_new |
+------------+------------+-------------+------------+
| NULL       | 0          | 0           | 1010       |
| NULL       | NULL       | NULL        | NULL       |
| NULL       | NULL       | 10100       | 11110      |
| 100        | 0          | NULL        | NULL       |
| NULL       | NULL       | NULL        | 110010     |
| 110        | 1          | 1           | NULL       |
| NULL       | NULL       | NULL        | NULL       |
| 1          | NULL       | NULL        | NULL       |
| NULL       | 10         | 10          | 0          |
| 1010       | NULL       | NULL        | NULL       |
+------------+------------+-------------+------------+

Related functions

CONV is a mathematical function. For more information about functions related to data computing and conversion, see Mathematical functions.