CONV function-MaxCompute(MaxCompute)-阿里云帮助中心

The CONV function converts a number from one base to another and returns the result as a string.

Command format

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

Parameters

Parameter	Required	Description
input	Yes	The value to convert. It must be of the STRING type. Note If your project is in Hive-compatible mode, which means you have run the `set odps.sql.hive.compatible=true;` command, the function supports implicit type conversion from BIGINT and DOUBLE. If your project is not in Hive-compatible mode, which means you have run the `set odps.sql.hive.compatible=false;` command, the function does not support implicit type conversion from BIGINT and DOUBLE.
from_base	Yes	The source base. The value must be a decimal integer: 2, 8, 10, or 16. Implicit type conversion is not supported.
to_base	Yes	The target base. The value must be a decimal integer: 2, 8, 10, or 16. Implicit type conversion is not supported.

Return value

Returns a value of the STRING type. The following rules apply:

If input, from_base, or to_base is NULL, the function returns NULL.
The conversion uses 64-bit precision. If an overflow occurs, the function returns NULL.
If input is a BIGINT or DOUBLE value and the project is not in Hive-compatible mode, the implicit conversion fails and the function returns NULL.
If input is a negative value, the function returns NULL in non-Hive-compatible mode. In Hive-compatible mode, the function returns 0.
If input is a decimal number, the function returns NULL in non-Hive-compatible mode. In Hive-compatible mode, the value is converted to an integer before the base conversion. The fractional 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

-- Disable strict mode.
SET odps.sql.udf.strict.mode=false;
-- Returns 12.
SELECT CONV('1100', 2, 10);
-- Returns c.
SELECT CONV('1100', 2, 16);
-- Returns 171.
SELECT CONV('ab', 16, 10);
-- Returns ab.
SELECT CONV('ab', 16, 16);
-- Returns NULL.
SELECT CONV('1100', null, 10);

Examples: Table data

Convert the values from the Sample data to binary format.

-- Disable strict mode.
SET odps.sql.udf.strict.mode=false;
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 mathematical functions, see Mathematical functions.