FORMAT_NUMBER is an additional function of MaxCompute V2.0. The FORMAT_NUMBER function converts an expression into a string in the specified format. For example, you can use this function to convert an expression into a string that uses thousands separators. You can also use this function to specify the number of decimal places that you want to retain. This topic describes the syntax and parameters of the FORMAT_NUMBER function. This topic also provides examples on how to use the FORMAT_NUMBER function.
Syntax
string format_number(float|double|decimal <expr1>, <expr2>)Parameters
expr1: required. This parameter specifies the expression that you want to format. A value of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.
expr2: required. This parameter specifies the format of the expression after the conversion. It can specify the number of decimal places that you want to retain. It can also be expressed in a format similar to
#,###,###.##.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
If expr2 is greater than 0, the value is rounded to the specified place after the decimal point.
If expr2 is equal to 0, the value has no decimal point or decimal part.
If expr2 is less than 0 or greater than 340, an error is returned.
If the value of expr1 or expr2 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 5.230.
select format_number(5.230134523424545456,3);
-- The return value is 12,332.123.
select format_number(12332.123456, '#,###,###,###.###');
-- The return value is null.
select format_number(null,3);Example: table data
Retain values in all columns to the specified number of decimal places 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 format_number(int_data, 1) as int_new, format_number(bigint_data, 1) as bigint_new, format_number(double_data, 2) as double_new, format_number(decimal_data, 1) as decimal_new, format_number(float_data, 0) as float_new, format_number(string_data, 1) as string_new from mf_math_fun_t;The following result is returned:
+---------+------------+------------+-------------+-----------+------------+
| int_new | bigint_new | double_new | decimal_new | float_new | string_new |
+---------+------------+------------+-------------+-----------+------------+
| NULL | -10.0 | 0.53 | 0.5 | 1 | 10.0 |
| -20.0 | NULL | -0.10 | -0.1 | -0 | -10.0 |
| 0.0 | -1.0 | NULL | 20.5 | -1 | 30.0 |
| -40.0 | 4.0 | 0.89 | NULL | 1 | -30.0 |
| 5.0 | -50.0 | -1.00 | -1.0 | NULL | 50.0 |
| -60.0 | 6.0 | 1.50 | 1.5 | 2 | -50.0 |
| -1.0 | -70.0 | -7.50 | -7.5 | -8 | NULL |
| -80.0 | 1.0 | -10.20 | -10.2 | -10 | -1.0 |
| 9.0 | -90.0 | 2.58 | 2.6 | 3 | 0.0 |
| -100.0 | 10.0 | -5.80 | -5.8 | -6 | -90.0 |
+---------+------------+------------+-------------+-----------+------------+Related functions
FORMAT_NUMBER is a mathematical function or a string function.
For more information about functions related to data computing and conversion, see Mathematical functions.
For more information about functions related to string searches and conversion, see String functions.