Salesforce Docs Portal

FORMAT_NUMBER

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Converts a numeric value to a formatted string using a pattern syntax.

Syntax

format_number(<value>, <pattern>)

Arguments

Required

  • <value>: The numeric value to format. Accepts any numeric type.
  • <pattern>: A varchar format pattern that controls how the number is rendered.

Returns

Returns a varchar string representation of <value> formatted according to <pattern>.

Returns NULL if either argument is NULL.

Pattern Syntax

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.

Subpatterns

A pattern can contain two subpatterns separated by ;:

positive_pattern;negative_pattern

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.

Considerations

  • The pattern syntax is similar to Java DecimalFormat and 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 0 in positions where you always want a digit displayed, even if it's zero.

Examples

Zero-Padding an Integer

Pad a number to four digits with leading zeros.

SELECT format_number(42, '0000') AS result;

Returns '0042'.

Decimal Places with Grouping Separator

Format a number with two decimal places and thousands grouping.

SELECT format_number(1234567.8, '#,##0.00') AS result;

Returns '1,234,567.80'.

Grouping Separator Without Decimals

Display a large integer with thousands separators.

SELECT format_number(9876543, '#,###') AS result;

Returns '9,876,543'.

Percentage Formatting

Multiply by 100 and append a percent sign.

SELECT format_number(0.753, '0.0%') AS result;

Returns '75.3%'.

Scientific Notation

Format a number in standard scientific notation.

SELECT format_number(12345.678, '0.###E0') AS result;

Returns '1.235E4'.

Positive and Negative Subpatterns

Use parentheses to denote negative values (accounting style).

SELECT format_number(-1234.5, '#,##0.00;(#,##0.00)') AS result;

Returns '(1,234.50)'.

Related Documentation