Custom Numeric Format Strings Supported by FORMAT() in SQL Server

Posted on by Ian

This article provides a reference for the custom numeric format specifiers that can be used when formatting numbers using the FORMAT() function in SQL Server. Examples included.

Format String Description/Example
0

Zero placeholder. Replaces the zero with the corresponding digit if one is present; otherwise, zero appears in the result string.

Examples:

SELECT 
  FORMAT(12.34, '0') R1,
  FORMAT(012.34, '00') R2,
  FORMAT(12.34, '000') R3,
  FORMAT(012.34, '0000') R4,
  FORMAT(12.54, '00') R5;
+------+------+------+------+------+
| R1   | R2   | R3   | R4   | R5   |
|------+------+------+------+------|
| 12   | 12   | 012  | 0012 | 13   |
+------+------+------+------+------+
#

Digit placeholder. Replaces the “#” symbol with the corresponding digit if one is present; otherwise, no digit appears in the result string.

No digit appears in the result string if the corresponding digit in the input string is a non-significant 0.

Examples:

SELECT 
  FORMAT(12.34, '#') R1,
  FORMAT(012.34, '##') R2,
  FORMAT(12.34, '###') R3,
  FORMAT(012.34, '####') R4,
  FORMAT(12.54, '##') R5;
+------+------+------+------+------+
| R1   | R2   | R3   | R4   | R5   |
|------+------+------+------+------|
| 12   | 12   | 12   | 12   | 13   |
+------+------+------+------+------+
.

Decimal point. Determines the location of the decimal separator in the result string.

Examples:

SELECT 
  FORMAT(12.34, '0.0', 'en-US') R1,
  FORMAT(12.34, '.00', 'en-US') R2,
  FORMAT(12.34, '.', 'en-US') R3,
  FORMAT(12.34, '.00', 'fr-FR') R4;
+------+-------+------+-------+
| R1   | R2    | R3   | R4    |
|------+-------+------+-------|
| 12.3 | 12.34 | 12   | 12,34 |
+------+-------+------+-------+
,

Group separator and number scaling. Can be used as both a group separator (also known as a thousand separator) and a number scaling specifier.

  • As a group separator, it inserts a localized group separator character between each group.
  • As a number scaling specifier, it divides a number by 1000 for each comma specified.

To specify a group separator, place one or more commas between two digit placeholders (0 or #) that format the integral digits of a number. This results in a group separator character being inserted between each number group in the integral part of the output.

To specify a number scaling specifier, place one or more commas immediately to the left of the explicit or implicit decimal point.

Examples – As a Group Separator:

SELECT 
  FORMAT(12345678, '#,#', 'en-US') 'US English',
  FORMAT(12345678, '#,#', 'fr-FR') 'French',
  FORMAT(12345678, '#,#', 'es-ES') 'Spanish';
+--------------+------------+------------+
| US English   | French     | Spanish    |
|--------------+------------+------------|
| 12,345,678   | 12 345 678 | 12.345.678 |
+--------------+------------+------------+

Examples – As a Number Scaling Specifier:

SELECT 
  FORMAT(12000, '#,', 'en-US') 'R1',
  FORMAT(1234567890, '#,', 'en-US') R2,
  FORMAT(1234567890, '#,,', 'en-US') R3;
+------+---------+------+
| R1   | R2      | R3   |
|------+---------+------|
| 12   | 1234568 | 1235 |
+------+---------+------+

Examples – As Both:

SELECT 
  FORMAT(1234567890, '#,#,', 'en-US') R1,
  FORMAT(1234567890, '#,#,,', 'en-US') R2;
+-----------+-------+
| R1        | R2    |
|-----------+-------|
| 1,234,568 | 1,235 |
+-----------+-------+
%

Percentage placeholder. Multiplies a number by 100 and inserts a localized percentage symbol in the result string.

Examples:

SELECT 
  FORMAT(0.1234, '#.# %', 'en-US') R1,
  FORMAT(0.1235, '#.#%', 'zh-cn') R2,
  FORMAT(0.125, '#. %', 'en-US') R3,
  FORMAT(0.1234, '%#.00,', 'tr') R4,
  FORMAT(0.1234, '#.#%', 'it') R5;
+--------+-------+------+--------+-------+
| R1     | R2    | R3   | R4     | R5    |
|--------+-------+------+--------+-------|
| 12.3 % | 12.4% | 13 % | %12,34 | 12,3% |
+--------+-------+------+--------+-------+
‰

Per mille placeholder. Multiplies a number by 1000 and inserts a localized per mille symbol in the result string.

Examples:

SELECT 
  FORMAT(0.01234, '#.# ‰', 'en-US') 'R1',
  FORMAT(0.01235, '#.# ‰', 'en-US') R2,
  FORMAT(0.0125, '#. ‰', 'en-US') R3,
  FORMAT(0.01234, '#.# ‰', 'fr-FR') R4;
+--------+--------+------+--------+
| R1     | R2     | R3   | R4     |
|--------+--------+------+--------|
| 12.3 ‰ | 12.4 ‰ | 13 ‰ | 12,3 ‰ |
+--------+--------+------+--------+

E0
E+0
E-0
e0
e+0
e-0

Exponential notation. If followed by at least one zero (0), formats the result using exponential notation. The case (E or e) indicates the case of the exponent symbol in the result string. The number of zeros following the E or e character determines the minimum number of digits in the exponent. A plus sign (+) indicates that a sign character always precedes the exponent. A minus sign (-) indicates that a sign character precedes only negative exponents.

Examples:

SELECT 
  FORMAT(123456789, '0e0') R1,
  FORMAT(123456789, '0e+0') R2,
  FORMAT(123456789, '0E+00') R3,
  FORMAT(1234.56789, '0.0##e+00') R4,
  FORMAT(12.3456789-12, '0e-0') R5;
+------+------+-------+-----------+------+
| R1   | R2   | R3    | R4        | R5   |
|------+------+-------+-----------+------|
| 1e8  | 1e+8 | 1E+08 | 1.235e+03 | 3e-1 |
+------+------+-------+-----------+------+

\

Escape character. Causes the next character to be interpreted as a literal rather than as a custom format specifier.

Example:

SELECT FORMAT(123, '\#0000') Result;
+----------+
| Result   |
|----------|
| #0123    |
+----------+

'string'
"string"

Literal string delimiter. Indicates that the enclosed characters should be copied to the result string unchanged.

Example:

SELECT FORMAT(23, '## Degrees') Result;
+------------+
| Result     |
|------------|
| 23 Degrees |
+------------+

;

Section separator. This is a conditional format specifier that defines sections with separate format strings for positive, negative, and zero numbers. This allows you to apply different formatting to a number depending on whether its value is positive, negative, or zero. A custom format string can contain up to three sections separated by semicolons.

  • One section: The format string applies to all values (just like in the previous examples).
  • Two sections: The first section applies to positive values and zeros, and the second section applies to negative values.

    If the number to be formatted is negative, but becomes zero after rounding according to the format in the second section, the resulting zero is formatted according to the first section.

  • Three sections: The first section applies to positive values, the second section applies to negative values, and the third section applies to zeros.

    The second section can be left empty (by having nothing between the semicolons), in which case the first section applies to all nonzero values.

    If the number to be formatted is nonzero, but becomes zero after rounding according to the format in the first or second section, the resulting zero is formatted according to the third section.

Note that negative values are always displayed without a minus sign when section separators are used. If you want the final formatted value to have a minus sign, you’ll need to explicitly include the minus sign as part of the custom format string. This also applies to any other preexisting formatting associated with a number.

Example – One Section (no section separators)
This is a typical format string consisting of one section (just like in the previous examples). Therefore, no separation separators are used.

SELECT 
  FORMAT(123, '0 (Number)') Positive,
  FORMAT(-123, '0 (Number)') Negative,
  FORMAT(0, '0 (Number)') Zero;
+--------------+---------------+------------+
| Positive     | Negative      | Zero       |
|--------------+---------------+------------|
| 123 (Number) | -123 (Number) | 0 (Number) |
+--------------+---------------+------------+

Note that the minus sign remains intact, because I didn’t use any section separators.

Examples – Two Sections:

/* EXAMPLE 1 - Same format string, different values */
SELECT 
  FORMAT(123,  '0 (Positive or Zero); 0 (Negative)') Positive,
  FORMAT(-123, '0 (Positive or Zero); 0 (Negative)') Negative,
  FORMAT(0,    '0 (Positive or Zero); 0 (Negative)') Zero;
+------------------------+-----------------+----------------------+
| Positive               | Negative        | Zero                 |
|------------------------+-----------------+----------------------|
| 123 (Positive or Zero) |  123 (Negative) | 0 (Positive or Zero) |
+------------------------+-----------------+----------------------+

/* EXAMPLE 2 - Rounding. 
Negative values rounded to zero get 
formatted under the first format string. 
*/
SELECT 
  FORMAT(0.1,  '0 (Positive or Zero); 0 (Negative)') Positive,
  FORMAT(-0.1, '0 (Positive or Zero); 0 (Negative)') Negative;
+----------------------+----------------------+
| Positive             | Negative             |
|----------------------+----------------------|
| 0 (Positive or Zero) | 0 (Positive or Zero) |
+----------------------+----------------------+

Examples – Three Sections:

/* EXAMPLE 1 - Basic Usage */
SELECT FORMAT(123, '0 (Positive); 0 (Negative); 0 (Zero)') Result;
+----------------+
| Result         |
|----------------|
| 123 (Positive) |
+----------------+

/* EXAMPLE 2 - Same format string, different values */
SELECT 
  FORMAT(123,   '0 (Positive); 0 (Negative); 0 (Zero)') 'Positive',
  FORMAT(-123,  '0 (Positive); 0 (Negative); 0 (Zero)') 'Negative',
  FORMAT(0,     '0 (Positive); 0 (Negative); 0 (Zero)') 'Zero',
  FORMAT(0.123, '0 (Positive); 0 (Negative); 0 (Zero)') 'Rounded to Zero';

+----------------+----------------+-----------+-------------------+
| Positive       | Negative       | Zero      | Rounded to Zero   |
|----------------+----------------+-----------+-------------------|
| 123 (Positive) | 123 (Negative) |  0 (Zero) |  0 (Zero)         |
+----------------+----------------+-----------+-------------------+

/* EXAMPLE 3 - Second format string is empty */
SELECT 
  FORMAT(123,   '0 (Positive);; 0 (Zero)') 'Positive',
  FORMAT(-123,  '0 (Positive);; 0 (Zero)') 'Negative',
  FORMAT(0,     '0 (Positive);; 0 (Zero)') 'Zero',
  FORMAT(0.123, '0 (Positive);; 0 (Zero)') 'Rounded to Zero';
+----------------+-----------------+-----------+-------------------+
| Positive       | Negative        | Zero      | Rounded to Zero   |
|----------------+-----------------+-----------+-------------------|
| 123 (Positive) | -123 (Positive) |  0 (Zero) |  0 (Zero)         |
+----------------+-----------------+-----------+-------------------+
Other

All other characters. The character is copied to the result string unchanged.

Example:

SELECT FORMAT(12, '# °C') Result;
+----------+
| Result   |
|----------|
| 12 °C    |
+----------+

The above list is a complete list of valid .NET custom format strings, based on the information from the official .NET documentation for Custom Format Strings on the Microsoft website at the time of writing.

The reason these are relevant to the SQL Server FORMAT() function is that it only accepts valid .NET Framework format strings.

In addition to the above custom format strings, you can also use standard format strings. Here’s a full list of Standard Numeric Format Strings that you can use with SQL Server.

You can also format date and time values. See the list of Standard Date and Time Format Strings as well as the Custom Date and Time Format Strings that you can use with the FORMAT() function.

If you’re still trying to understand what a format string even is, see What is a Format String in SQL Server? for a basic understanding of format strings and how they pertain to the FORMAT() function.