The BIN function of MaxCompute is an additional function of MaxCompute V2.0. This function converts a number into a binary string. This topic describes the syntax and parameters of the BIN function. This topic also provides examples on how to use the BIN function.
Syntax
string bin(<number>)Parameters
number: required. A value of the BIGINT, INT, SMALLINT, or TINYINT type.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
If number is not of the BIGINT, INT, SMALLINT, or TINYINT type, an error is returned.
If the value of number is 0, 0 is returned.
If the value of number is null, null is returned.
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 |
+------------+-------------+-------------+--------------+------------+-------------+Example: static data
-- The return value is 0.
select bin(0);
-- The return value is null.
select bin(null);
-- The return value is 1100.
select bin(12);Example: table data
Calculate the binary code of values in the int_data and bigint_data columns based on the sample data. Sample statement:
-- Enable the MaxCompute V2.0 data type edition. Commit this command along with the SQL statement.
set odps.sql.type.system.odps2=true;
select bin(int_data) as int_new, bin(bigint_data) as bigint_new from mf_math_fun_t;The following result is returned:
+----------------------------------------------------------------------------+------------------------------------------------------------------+
| int_new | bigint_new |
+----------------------------------------------------------------------------+------------------------------------------------------------------+
| NULL | 1111111111111111111111111111111111111111111111111111111111110110 |
| 1111111111111111111111111111111111111111111111111111111111101100 | NULL |
| 0 | 1111111111111111111111111111111111111111111111111111111111111111 |
| 1111111111111111111111111111111111111111111111111111111111011000 | 100 |
| 101 | 1111111111111111111111111111111111111111111111111111111111001110 |
| 1111111111111111111111111111111111111111111111111111111111000100 | 110 |
| 1111111111111111111111111111111111111111111111111111111111111111 | 1111111111111111111111111111111111111111111111111111111110111010 |
| 1111111111111111111111111111111111111111111111111111111110110000 | 1 |
| 1001 | 1111111111111111111111111111111111111111111111111111111110100110 |
| 1111111111111111111111111111111111111111111111111111111110011100 | 1010 |
+----------------------------------------------------------------------------+------------------------------------------------------------------+Related functions
BIN is a mathematical function. For more information about functions related to data computing and conversion, see Mathematical functions.