Format a numeric value as a thousands-separated string with a fixed number of decimal places.
FORMAT_NUMBER(num, d)## Overview Formats a numeric value as a string with grouping separators (commas) at every three digits to the left of the decimal point and a fixed number of digits to the right. The decimal portion is rounded half-up to the requested precision. Use this function for report-ready numeric display. ## Behavior - Returns NULL if any argument is NULL. - Uses `,` as the thousands separator and `.` as the decimal point. - Rounds half-up at the requested precision. - A negative `num` keeps its sign in the output. - A `d` of 0 produces an integer string with no decimal point. - Does not include a currency symbol; prefix with your own if needed. - Deterministic and side effect free. ## Compatibility - Matches the standard analytical SQL FORMAT_NUMBER semantics.
| Name | Type | Description |
|---|---|---|
num | Specifies the numeric value to format. Accepts integer or floating-point input. | |
d | Specifies the number of decimal places to render. Must be a non-negative integer; the value is rounded half-up to this precision. |
-- Two decimal places with thousands separators
SELECT FORMAT_NUMBER(1234567.891, 2); -- '1,234,567.89'-- Integer output (no decimal places)
SELECT FORMAT_NUMBER(1234567.891, 0); -- '1,234,568'-- Pad to four decimal places
SELECT FORMAT_NUMBER(3.1, 4); -- '3.1000'-- Negative numbers preserve the sign
SELECT FORMAT_NUMBER(-1234.5, 1); -- '-1,234.5'-- NULL propagation
SELECT FORMAT_NUMBER(NULL, 2); -- NULL-- Format a currency column for a report
SELECT order_id, CONCAT('$', FORMAT_NUMBER(total_amount, 2)) AS total
FROM sales.catalog.orders;