FORMAT_NUMBER
Description
The FORMAT_NUMBER function formats numerical values into strings with unit symbols. Supported units are: K (thousand), M (million), B (billion), T (trillion), Q (quadrillion).
Syntax
FORMAT_NUMBER(<val>)
Parameters
| Parameter | Description |
|---|---|
<val> | The numerical value to be formatted. Type: DOUBLE |
Return Value
Returns VARCHAR type, representing the formatted string with unit symbol.
Special cases:
- If the parameter is NULL, returns NULL
- Numbers less than 1000 are returned directly without units
- Unit conversion rules:
- K: thousand (1,000)
- M: million (1,000,000)
- B: billion (1,000,000,000)
- T: trillion (1,000,000,000,000)
- Q: quadrillion (1,000,000,000,000,000)
Examples
- Basic usage: thousand (K)
SELECT format_number(1500);
+---------------------+
| format_number(1500) |
+---------------------+
| 1.50K |
+---------------------+
- Million (M)
SELECT format_number(5000000);
+------------------------+
| format_number(5000000) |
+------------------------+
| 5.00M |
+------------------------+
- Numbers less than thousand
SELECT format_number(999);
+----------------------------------+
| format_number(cast(999 as DOUBLE))|
+----------------------------------+
| 999 |
+----------------------------------+
- NULL value handling
SELECT format_number(NULL);
+---------------------+
| format_number(NULL) |
+---------------------+
| NULL |
+---------------------+
