SQL Server provides us with a quick and easy way to format numbers with commas inserted at the relevant place. For example, 1234.56 can become 1,234.56. Or it can become 1.234,56, if that’s the locale that you’re using.

Example

We can use the FORMAT() function to format numbers with commas. When we use this function, we pass the number and a format string. The format string determines how the number will be formatted when returned.

The FORMAT() function returns a formatted string representation of the number, based on the values we provide.

Here’s an example to demonstrate:

SELECT 
    FORMAT(123456.789, 'N') AS "Number",
    FORMAT(123456.789, 'P') AS "Percent",
    FORMAT(123456.789, 'C') AS "Currency";

Result:

+------------+----------------+-------------+
| Number     | Percent        | Currency    |
|------------+----------------+-------------|
| 123,456.79 | 12,345,678.90% | $123,456.79 |
+------------+----------------+-------------+

This example demonstrates three of the standard numeric format strings:

• If we just want the formatted number, we can use N
• If we want it formatted as a percentage, we can use P
• For currency, we can use C.

SQL Server is smart enough to know your current locale (based on the language of the current session), and formats the result according to that locale’s conventions. This is important, because not everyone lives in a country that uses a comma as the thousands separator, and a full stop as the decimal separator.

Furthermore, the FORMAT() function accepts a third optional “culture” argument, which enables you to explicitly specify the locale.

Here’s an example of what I mean:

SELECT 
    FORMAT(123456.789, 'N', 'de-de') AS "Number",
    FORMAT(123456.789, 'P', 'de-de') AS "Percent",
    FORMAT(123456.789, 'C', 'de-de') AS "Currency";

Result:

+------------+-----------------+--------------+
| Number     | Percent         | Currency     |
|------------+-----------------+--------------|
| 123.456,79 | 12.345.678,90 % | 123.456,79 € |
+------------+-----------------+--------------+

In this case I specify de-de as the culture, which means that the results will be formatted according to German conventions. We can see that full stops are used as the group separator, and a comma is used for the decimal separator.

If the third argument is omitted, the language of the current session is used.

Custom Format Strings

It’s also possible to use custom format specifiers to construct your own custom format strings. For example, the # character is a digit placeholder, the 0 is a zero placeholder, the comma (,) is a placeholder for the group separator, and the full stop (.) is a placeholder for the decimal separator.

This is true regardless of the locale being used – SQL Server will work out which characters to use for the group and decimal separators based on the current locale.

Example:

SELECT 
    FORMAT(123456.789, '###,###,###.##', 'en-us') AS "US English",
    FORMAT(123456.789, '###,###,###.##', 'de-de') AS "German";

Result:

+--------------+------------+
| US English   | German     |
|--------------+------------|
| 123,456.79   | 123.456,79 |
+--------------+------------+

In both cases, I used the same format string, yet SQL Server returned the group and decimal separators that were applicable to the specified locale (which was specified with the third “culture” argument).

In the above example, the format string includes more digits than the actual number. Regardless, SQL Server chose not to return any leading zeros. That’s because we used the # format specifier.

If we want SQL Server to return leading zeros, we can use the 0 format specifier:

SELECT 
    FORMAT(123456.789, '000,000,000.00', 'en-us') AS "US English",
    FORMAT(123456.789, '000,000,000.00', 'de-de') AS "German";

Result:

+----------------+----------------+
| US English     | German         |
|----------------+----------------|
| 000,123,456.79 | 000.123.456,79 |
+----------------+----------------+