FORMAT_ROUND

描述

将数字格式化为类似于“#,###,###.##”的格式，四舍五入到指定位小数，并将结果作为字符串返回。

提示

该函数自 3.0.6 版本开始支持.

语法

FORMAT_ROUND(<number>, <D>)

参数

参数	说明
<number>	需要被格式化的数字
<D>	小数位数，范围 [0, 1024]

返回值

返回 STRING 类型的格式化字符串。特殊情况：

• 当参数为 NULL 时，返回 NULL
• 如果 D 为 0，结果将没有小数点或小数部分。Doris 4.1 系列自 4.1.5 起，DECIMAL 输入在 D 为 0 时也会四舍五入；此前可能直接丢弃小数部分。
• 如果 D 不在 [0, 1024] 范围内，将返回错误提示参数应在该范围内。

示例

select format_round(17014116, 2);

+---------------------------+
| format_round(17014116, 2) |
+---------------------------+
| 17,014,116.00             |
+---------------------------+

select format_round(1123.456, 2);

+---------------------------+
| format_round(1123.456, 2) |
+---------------------------+
| 1,123.46                  |
+---------------------------+

select format_round(1123.4, 2);

+-------------------------+
| format_round(1123.4, 2) |
+-------------------------+
| 1,123.40                |
+-------------------------+

select format_round(123456, 0);

+-------------------------+
| format_round(123456, 0) |
+-------------------------+
| 123,456                 |
+-------------------------+

select format_round(123456, 3);

+-------------------------+
| format_round(123456, 3) |
+-------------------------+
| 123,456.000             |
+-------------------------+

select format_round(123456.123456, 0);

+--------------------------------+
| format_round(123456.123456, 0) |
+--------------------------------+
| 123,456                        |
+--------------------------------+

select format_round(123456.123456, 3);

+--------------------------------+
| format_round(123456.123456, 3) |
+--------------------------------+
| 123,456.123                    |
+--------------------------------+

select format_round(123456.123456, 6);

+--------------------------------+
| format_round(123456.123456, 6) |
+--------------------------------+
| 123,456.123456                 |
+--------------------------------+

SELECT format_round(-0.01, -1);

ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT]The second argument is -1, it should be in range [0, 1024].

SELECT format_round(-0.01, -1500);

ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT]The second argument is -1500, it should be in range [0, 1024].

DECIMAL 输入舍入到 0 位小数：

SELECT FORMAT_ROUND(CAST(1.9 AS DECIMAL(9, 1)), 0),
       FORMAT_ROUND(CAST(-1.9 AS DECIMAL(9, 1)), 0),
       FORMAT_ROUND(CAST(1.4 AS DECIMAL(9, 1)), 0),
       FORMAT_ROUND(CAST(-1.4 AS DECIMAL(9, 1)), 0);

2    -2    1    -1

NULL 输入：

SELECT FORMAT_ROUND(NULL, 2);

NULL