MaxCompute FORMAT_NUMBER function introduction - MaxCompute

The FORMAT_NUMBER function, a MaxCompute 2.0 extension, formats numbers with specified patterns, such as thousand separators and decimal places. This topic explains its syntax, parameters, and provides usage examples.

Syntax

STRING FORMAT_NUMBER(DOUBLE|BIGINT|INT|SMALLINT|TINYINT|FLOAT|DECIMAL|STRING <expr1>, INT <expr2>)

Parameters

expr1: required. This parameter specifies the expression you want to format. It accepts values of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.
expr2: required. A value of the INT type. Valid values: 0 to 340. This parameter specifies the number of decimal places to retain. It can also be expressed in a format similar to #,###,###.##. The number of decimal places returned varies based on this parameter's value.

Return value

A value of the STRING type is returned. The return value follows these rules:

When 0<expr2≤340, the value is rounded to the specified place after the decimal point.
If expr2=0, the value has no decimal point or decimal part.
If expr2<0 or expr2>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         |
+------------+-------------+-------------+--------------+------------+-------------+

Examples: static data

-- The return value is 5.230.
SELECT FORMAT_NUMBER(5.230134523424545456,3);
-- The value 12,332.123 is returned.
SELECT FORMAT_NUMBER(12332.123456, '#,###,###,###.###');
-- The return value is NULL.
SELECT FORMAT_NUMBER(NULL,3);

Examples: 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      |
+---------+------------+------------+-------------+-----------+------------+