FORMAT_NUMBER

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

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

1format_number(<value>, <pattern>)

• <value>: The numeric value to format. Accepts any numeric type.
• <pattern>: A varchar format 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:

A pattern can contain two subpatterns separated by ;:

1positive_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.

• 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.

Pad a number to four digits with leading zeros.

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

Returns '0042'.

Format a number with two decimal places and thousands grouping.

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

Returns '1,234,567.80'.

Display a large integer with thousands separators.

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

Returns '9,876,543'.

Multiply by 100 and append a percent sign.

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

Returns '75.3%'.

Format a number in standard scientific notation.

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

Returns '1.235E4'.

Use parentheses to denote negative values (accounting style).

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

Returns '(1,234.50)'.

• TO_CHAR
• Formatting Functions