Starting from SQL Server 2012, you can format numeric types using the T-SQL FORMAT() function. This function accepts three arguments; the number, the format, and an optional “culture” argument.
It returns a formatted string of type nvarchar.
The format is supplied as a format string. A format string defines how the output should be formatted.
Here’s an example:
SELECT FORMAT(1, 'N');
Result:
1.00
In this case, I used N as the second argument. This is the standard numeric format specifier for Number. This particular format specifier (N) results in the output being formatted with integral and decimal digits, group separators, and a decimal separator with optional negative sign. This argument is case-insensitive, so either N or n is fine.
Decimal Places
Here’s another example, this time using N1 to specify that we only want one decimal place:
SELECT FORMAT(1, 'N1');
Result:
1.0
But you can also increase the number of decimal places too:
SELECT FORMAT(1, 'N7');
Result:
1.0000000
In these examples, the 1 and 7 are known as precision specifiers.
Precision specifiers are optional. They can be a value from 0 to 99, which specifies the precision of the result. How it works depends on the format specifier being used. For some format specifiers, it will specify the total number of digits in the result, for others it will specify the number of decimal places. In other cases it will be ignored altogether. See the full list of Standard Numeric Format specifiers to see how precision specifiers affect the output from each format specifier.
Percent
The following examples use the P argument, which formats the number as a percentage value. It multiplies the number by 100, then adds a localized percent sign.
SELECT
FORMAT(1, 'P') AS 'Example 1',
FORMAT(.375, 'P') AS 'Example 2',
FORMAT(.0375, 'P', 'tr-tr') AS 'Example 3';
Result:
Example 1 Example 2 Example 3 --------- --------- --------- 100.00 % 37.50 % %3,75
Note that “Example 3” includes a third argument, which specifies the culture to be used in the output. In this case, I use tr-tr for Turkish. This results in the percent sign being prepended to the number (instead of appended, like the others). It also results in a comma being used as the decimal separator.
If you don’t provide the “culture” argument, the language of the current session is used.
As you might imagine, you could get quite different results depending on your current language or the value of any “culture” argument. See How Language Settings can Affect your FORMAT() Results for more examples.
Currency
You can use C to return a currency value.
SELECT FORMAT(1, 'C');
Result:
$1.00
You can also add in the locale as a third parameter.
Here are some examples:
SELECT
FORMAT(1, 'C', 'fr-FR') AS France,
FORMAT(1, 'C', 'th-TH') AS Thailand,
FORMAT(1, 'C', 'ja-JP') AS Japan;
Result:
France Thailand Japan ------ -------- ----- 1,00 € ฿1.00 ¥1
Exponential (Scientific)
You can use E to specify exponential notation.
SELECT FORMAT(1234.56789, 'E');
Result:
1.234568E+003
Hexadecimal
You can even format a number as hexadecimal. To do this, use the X argument:
SELECT FORMAT(10, 'X');
Result:
A
Hexadecimal is base 16. Therefore, the count goes 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F before starting again (e.g. 10 in hexadecimal is the equivalent of 16 in decimal, and 11 is the equivalent of 17, etc). That’s why the example here results in A, because in hexadecimal, A comes after 9.
Here’s another example, this time with a larger number:
SELECT FORMAT(7145, 'X');
Result:
1BE9
Custom Format
You can also create your own custom format by using a custom numeric format string. This consists of a series of custom numeric format specifiers that provide a pattern for your number.
This is easier explained with an example:
SELECT FORMAT(123456789, '##-###-####');
Result:
12-345-6789
In this case, I use the digit placeholder (#). The digit placeholder allows me to specify a format for the number, using the # symbol as a placeholder for each number.
Commas
Another example:
SELECT FORMAT(123456789, '#,#');
Result:
123,456,789
Leading Zeros
Another custom numeric format specifier is zero (0). You can use this to pad a number with leading zeros:
SELECT
FORMAT(7, '000') AS 'Example 1',
FORMAT(123, '0000') AS 'Example 2',
FORMAT(123, '00000') AS 'Example 3';
Result:
Example 1 Example 2 Example 3 --------- --------- --------- 007 0123 00123
You can also use it to remove zeros:
SELECT
FORMAT(0123, '000') AS 'Example 1',
FORMAT(0123, '0000') AS 'Example 2',
FORMAT(000123, '00000') AS 'Example 3';
Result:
Example 1 Example 2 Example 3 --------- --------- --------- 123 0123 00123
Numeric Format Specifiers Reference
The following articles contain all the numeric format specifiers you can use with the FORMAT() function, along with T-SQL examples.
These are the same format specifiers that are supported by the .NET Framework (the FORMAT() function relies on the .NET Framework).
Date and Time
The FORMAT() function also allows you to format date and time.
Date and time has a separate set of format specifiers: