This article provides a reference for the standard numeric format specifiers that can be used when formatting numbers using the FORMAT() function in SQL Server. Examples included.
Some of the examples use precision specifiers (these consist of one or two digits appended to the format specifier). Precision specifiers 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.
| Format String | Description/Example |
|---|---|
C or c |
Currency. Converts the number to a string that represents a currency amount. An optional precision specifier can be used to specify the number of decimal places. Examples (using various cultures): SELECT FORMAT(12.34, 'C', 'en-us') R1, FORMAT(012.34, 'C', 'iv') R2, FORMAT(12.34, 'C0', 'ja-jp') R3, FORMAT(12.54, 'c', 'vi-vn') R4, FORMAT(12.54, 'c0', 'en-au') R5, FORMAT(12.54, 'c4', 'en-gb') R6; +--------+--------+------+---------+------+----------+ | R1 | R2 | R3 | R4 | R5 | R6 | |--------+--------+------+---------+------+----------| | $12.34 | ¤12.34 | ¥12 | 12,54 ₫ | $13 | £12.5400 | +--------+--------+------+---------+------+----------+ |
D or d |
Decimal. Converts the number to a string of decimal digits (0-9), prefixed by a minus sign if the number is negative. This format is supported only for integral types. If the (optional) precision specifier is used, it specifies the total number of digits in the result. If the number doesn’t have that many digits, it will be padded with zeros to its left. Examples: SELECT FORMAT(123, 'D') R1, FORMAT(123, 'D0') R2, FORMAT(123, 'D3') R3, FORMAT(123, 'd4') R4, FORMAT(0123, 'd5') R5, FORMAT(1.23, 'd') R6; +------+------+------+------+-------+------+ | R1 | R2 | R3 | R4 | R5 | R6 | |------+------+------+------+-------+------| | 123 | 123 | 123 | 0123 | 00123 | NULL | +------+------+------+------+-------+------+ |
E or e |
Exponential (scientific). Converts the number to a string of the form “-d.ddd…E+ddd” or “-d.ddd…e+ddd”, where each “d” indicates a digit (0-9). The string starts with a minus sign if the number is negative. Exactly one digit always precedes the decimal point. If a precision specifier is used, it specifies how many digits are to the right of the decimal point. If omitted, six digits will appear to the right of the decimal point. Examples: SELECT FORMAT(1024.1234567, 'E') R1, FORMAT(1024.1234567, 'E0') R2, FORMAT(1024.1234567, 'E3') R3, FORMAT(1024.1234567, 'e4') R4, FORMAT(1024.1234567, 'e7') R5; +---------------+--------+------------+-------------+----------------+ | R1 | R2 | R3 | R4 | R5 | |---------------+--------+------------+-------------+----------------| | 1.024123E+003 | 1E+003 | 1.024E+003 | 1.0241e+003 | 1.0241235e+003 | +---------------+--------+------------+-------------+----------------+ |
F or f |
Fixed-point. Converts the number to a string of the form “-ddd.ddd…” where each “d” indicates a digit (0-9). The string starts with a minus sign if the number is negative. An optional precision specifier can be used to specify the number of decimal places. Examples: SELECT FORMAT(12.345, 'F', 'en-us') R1, FORMAT(12.345, 'F0', 'en-us') R2, FORMAT(12.345, 'f3', 'en-us') R3, FORMAT(12.345, 'f4', 'en-us') R4, FORMAT(12.345, 'f5', 'en-us') R5, FORMAT(12.345, 'f', 'vi-vn') R6; +-------+------+--------+---------+----------+-------+ | R1 | R2 | R3 | R4 | R5 | R6 | |-------+------+--------+---------+----------+-------| | 12.35 | 12 | 12.345 | 12.3450 | 12.34500 | 12,35 | +-------+------+--------+---------+----------+-------+ |
G or g |
General. Converts the number to the more compact of either fixed-point or scientific notation, depending on the type of the number and whether a precision specifier is present. If the (optional) precision specifier is used, it defines the maximum number of significant digits that can appear in the result string. If omitted, or if the value is zero, the precision is determined by the number type. See the official .NET documentation for an explanation of how the number type can affect the precision. You may need to cross-reference that with the official documentation for Examples: SELECT FORMAT(12.345, 'G', 'en-us') R1, FORMAT(12.345, 'G0', 'en-us') R2, FORMAT(12.345, 'g3', 'en-us') R3, FORMAT(12.345, 'g4', 'en-us') R4, FORMAT(12.345, 'g5', 'en-us') R5, FORMAT(12.345, 'g', 'vi-vn') R6; +--------+--------+------+-------+--------+--------+ | R1 | R2 | R3 | R4 | R5 | R6 | |--------+--------+------+-------+--------+--------| | 12.345 | 12.345 | 12.3 | 12.35 | 12.345 | 12,345 | +--------+--------+------+-------+--------+--------+ |
N or n |
Number. Converts the number to a string of the form “-d,ddd,ddd.ddd…”. If the (optional) precision specifier is used, it defines the number of digits to the right of the decimal point. Examples: SELECT FORMAT(1234.56, 'N', 'en-us') R1, FORMAT(1234.56, 'N0', 'en-us') R2, FORMAT(1234.56, 'n3', 'en-us') R3, FORMAT(1234.56, 'n4', 'en-us') R4, FORMAT(1234.56, 'n', 'vi-vn') R5; +----------+-------+-----------+------------+----------+ | R1 | R2 | R3 | R4 | R5 | |----------+-------+-----------+------------+----------| | 1,234.56 | 1,235 | 1,234.560 | 1,234.5600 | 1.234,56 | +----------+-------+-----------+------------+----------+ |
P or p |
Percent. Multiplies the number by 100 and converts it to a string that represents a percentage. If the precision specifier is used, it defines the number of decimal places. Examples: SELECT FORMAT(.1234, 'P', 'en-us') R1, FORMAT(.1234, 'P0', 'en-us') R2, FORMAT(.1250, 'p0', 'en-us') R3, FORMAT(.1250, 'p3', 'en-us') R4, FORMAT(.1250, 'p', 'vi-vn') R5; +---------+------+------+----------+--------+ | R1 | R2 | R3 | R4 | R5 | |---------+------+------+----------+--------| | 12.34 % | 12 % | 13 % | 12.500 % | 12,50% | +---------+------+------+----------+--------+ |
R or r |
Round-trip. Attempts to ensure that a numeric value that is converted to a string is parsed back into the same numeric value. The precision specifier is ignored when using this format specifier. NOTE: In .NET, this format specifier is only supported by the Single, Double, and BigInteger number types. Also, for Double values, this format specifier in some cases fails to successfully round-trip the original value. It also offers poor performance when used with the Double and Single types. Further, the .NET documentation recommends that this format specifier is used for the BigInteger type only. It recommends that Double types use What does this mean for SQL Server? Basically you should probably avoid this format specifier in SQL Server. Instead, use Examples DECLARE @myFloat float, @myReal real; SET @myFloat = '123456789.1234567'; SET @myReal = @myFloat; SELECT @myFloat 'Float Value', @myReal 'Real Value'; SELECT FORMAT(@myFloat, 'R') 'Float R', FORMAT(@myFloat, 'G17') 'Float G17', FORMAT(@myReal, 'R') 'Real R', FORMAT(@myReal, 'G9') 'Real G9'; +------------------+--------------+ | Float Value | Real Value | |------------------+--------------| | 123456789.123457 | 1.234568E+08 | +------------------+--------------+ (1 row affected) +-------------------+-------------------+-----------+-----------+ | Float R | Float G17 | Real R | Real G9 | |-------------------+-------------------+-----------+-----------| | 123456789.1234567 | 123456789.1234567 | 123456792 | 123456792 | +-------------------+-------------------+-----------+-----------+ (1 row affected) |
X or x |
Hexadecimal. Converts the number to a string of hexadecimal digits. The case used (i.e. This format specifier is supported only for integral types. If the (optional) precision specifier is used, it specifies the total number of digits in the result. If the number doesn’t have that many digits, it will be padded with zeros to its left. Examples: SELECT FORMAT(123, 'X') R1, FORMAT(123, 'x') R2, FORMAT(123, 'x6') R3, FORMAT(12345678, 'x') R4, FORMAT(123, 'x6') R5, FORMAT(-123, 'x') R6, FORMAT(1.23, 'X') R7; +------+------+--------+--------+--------+----------+------+ | R1 | R2 | R3 | R4 | R5 | R6 | R7 | |------+------+--------+--------+--------+----------+------| | 7B | 7b | 00007b | bc614e | 00007b | ffffff85 | NULL | +------+------+--------+--------+--------+----------+------+ |
The above list is a complete list of valid .NET custom numeric format strings, based on the information from the official .NET documentation for Standard Numeric Format Strings on the Microsoft website at the time of writing.
The reason these are relevant to the SQL Server FORMAT() function is that this function only accepts valid .NET Framework format strings.
In addition to the above standard format strings, you can also use custom format strings. Here’s a full list of Custom 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.