The FORMAT () function format date/time and number values. This post will focus on formatting numbers in SQL Server queries. The FORMAT() function formats a value with the specified format. It has many different options for formatting a number.
Here I summarize some examples of the use of the FORMAT function used for numbers:
|
Format |
Query |
Result |
|
Numeric Format with
thousand separator and 2 decimal (default) |
SELECT
FORMAT(1234.4321, 'N', 'en-us') AS 'Number' |
1,234.43 |
|
Numeric Format with
thousand separator and 1 decimal (default) |
SELECT
FORMAT(1234.4321, 'N1', 'en-us') AS 'Number' |
1,234.4 |
|
Exponential,
Scientific notation |
SELECT FORMAT(1234.4321,
'E', 'en-us') AS 'Number' |
1.234432E+003 |
|
Exponential,
Scientific notation, with 2 decimals |
SELECT
FORMAT(1234.4321, 'E2', 'en-us') AS 'Number' |
1.23E+003 |
|
Digit without
decimal. |
SELECT FORMAT(1234,
'D', 'en-us') AS 'Number' |
1234 |
|
6 Digit number (with
additional “0”) |
SELECT FORMAT(1234,
'D6', 'en-us') AS 'Currency Format' |
001234 |
|
General Format |
SELECT
FORMAT(1234.4321, 'G', 'en-us') AS 'Number' |
1234.4321 |
|
General format, 6
digits |
SELECT
FORMAT(1234.4321, 'G6', 'en-us') AS 'Number' |
1234.43 |
|
Currency-England |
SELECT
FORMAT(100.27, 'C', 'en-GB') AS 'Number' |
£100.27 |
|
Currency-China |
SELECT
FORMAT(1234.4321, 'C', 'zh-CN') AS 'Number' |
¥1,234.43 |
|
Percentage |
SELECT FORMAT(0.5,
'P', 'en-us') AS 'number' |
50.00 % |
|
Percentage with 4
decimals |
SELECT FORMAT(0.5,
'P4', 'en-us') AS 'number' |
50.0000 % |
|
Hexadecimal format |
SELECT FORMAT(12344,
'X', 'en-us') AS 'number' |
3038 |
|
Phone number |
SELECT
FORMAT(123456789,'+###-###-####') AS 'number' |
+12-345-6789 |
|
Fixed point |
SELECT FORMAT(1234.4321,
'F', 'en-us') AS 'Number' |
1234.43 |
|
Fixed point – 8
digits |
SELECT
FORMAT(1234.4321, 'F8', 'en-us') AS 'Number' |
1234.43210000 |
Result samples:
0 Comments