FORMAT_NUMBER
Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API
Converts a numeric value to a formatted string using a pattern syntax.
<value>: The numeric value to format. Accepts any numeric type.<pattern>: Avarcharformat pattern that controls how the number is rendered.
Returns a varchar string representation of <value> formatted according to <pattern>.
Returns NULL if either argument is NULL.
The pattern syntax admits the following characters:
| Character | Meaning |
|---|---|
0 | Required digit position. Outputs 0 if no digit is present at that position. |
# | Optional digit position. Outputs nothing if no digit is present at that position. |
. | Decimal separator. |
, | Grouping separator (thousands, and so on). Can define arbitrary group sizes. |
E | Separates the mantissa and exponent in scientific notation (for example, 0.###E0). |
% | Multiplies the value by 100 and appends a percent sign. |
‰ | Multiplies the value by 1000 and appends a per mille sign. |
¤ | Currency sign. Always outputs $ for a single ¤, or USD for ¤¤. Not locale-configurable. |
; | Separates positive and negative subpatterns. |
- | Minus sign. |
' | Quotes literal text in the pattern. Use '' to include a literal single quote. |
A pattern can contain two subpatterns separated by ;:
If you don’t provide a negative subpattern, the negative pattern defaults to the positive pattern prefixed with -. When a negative subpattern is provided, only its prefix and suffix are used; the number formatting is always taken from the positive subpattern.
- The pattern syntax is similar to Java
DecimalFormatand Unicode CLDR number patterns. Data 360 SQL supports only a subset of those standards — not all pattern features from those specifications are available. - Returns NULL if either
<value>or<pattern>is NULL. - Use
#in positions where you want to suppress leading or trailing zeros. - Use
0in positions where you always want a digit displayed, even if it’s zero.
Pad a number to four digits with leading zeros.
Returns '0042'.
Format a number with two decimal places and thousands grouping.
Returns '1,234,567.80'.
Display a large integer with thousands separators.
Returns '9,876,543'.
Multiply by 100 and append a percent sign.
Returns '75.3%'.
Format a number in standard scientific notation.
Returns '1.235E4'.
Use parentheses to denote negative values (accounting style).
Returns '(1,234.50)'.