Converts a number from one number system to another.
Syntax
string conv(<input>, bigint <from_base>, bigint <to_base>)Parameters
input: required. The value is the integer you want to convert, which is of the STRING type. If the input value is of the BIGINT or DOUBLE type, the value is implicitly converted into a value of the STRING type before calculation.
from_base and to_base: required. The values of these parameters are decimal numbers. The values can be 2, 8, 10, or 16. If the input value is of the STRING or DOUBLE type, the value is implicitly converted into a value of the BIGINT type before calculation.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
If the value of 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 the value of input is a negative value, null is returned. If the value of input is a decimal, the value is converted into an integer before the conversion of number systems. The decimal part is left out.
Sample data
This section provides sample source data and examples for you to understand how to use the functions. 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 |
+------------+-------------+-------------+--------------+------------+-------------+Example: static data
-- The return value is 12.
select conv('1100', 2, 10);
-- The return value is C.
select conv('1100', 2, 16);
-- The return value is 171.
select conv('ab', 16, 10);
-- The return value is AB.
select conv('ab', 16, 16);
-- The return value is null.
select conv('1100', null, 10);Example: table data
Convert a number into a binary value based on the Sample data. 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.