# `FORMAT_NUMBER`

**Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API

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

## Syntax

```sql
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`](https://docs.oracle.com/en/java/se/17/docs/api/java.base/java/text/DecimalFormat.html) and [Unicode CLDR number patterns](https://cldr.unicode.org/translation/number-currency-formats/number-and-currency-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.

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

Returns `'0042'`.

### Decimal Places with Grouping Separator

Format a number with two decimal places and thousands grouping.

```sql
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.

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

Returns `'9,876,543'`.

### Percentage Formatting

Multiply by 100 and append a percent sign.

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

Returns `'75.3%'`.

### Scientific Notation

Format a number in standard scientific notation.

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

Returns `'1.235E4'`.

### Positive and Negative Subpatterns

Use parentheses to denote negative values (accounting style).

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

Returns `'(1,234.50)'`.

## Related Documentation

- [TO_CHAR](/docs/data/data-cloud-query-guide/references/dc-sql-reference/to-char.md)
- [Formatting Functions](/docs/data/data-cloud-query-guide/references/dc-sql-reference/formatting-func.md)