SQL 2005+

SQL Server Numeric Formatting

by Richard Carr, published at http://www.blackwasp.co.uk/SQLNumericFormats.aspx

Transact-SQL includes the Convert command that allows values to be converted between differing data types. When converting some numeric types to text, a style code may be applied. This code determines the formatting of the generated string.

Converting a Float or Real to a VarChar

You can use the Transact-SQL (T-SQL) Convert command to convert data between different types. When converting some numeric types to textual data, the resultant string has no formatting, as you may expect. For Float and Real data the final text result may be a simple number or may be presented using scientific notation. For these data types you can apply a style code that determines the formatting to use for the scientific notation. You can see the available styles by executing the following script:

DECLARE @value FLOAT
SET @value = 123456789.123456789

SELECT convert(VARCHAR, @value)     -- 1.23457e+008
SELECT convert(VARCHAR, @value, 0)  -- 1.23457e+008
SELECT convert(VARCHAR, @value, 1)  -- 1.2345679e+008
SELECT convert(VARCHAR, @value, 2)  -- 1.234567920000000e+008

Float and Real Style Codes

The table below describes the available numeric style codes for use with Float or Real data. The example values are generated from converting a Float value of 123456789.123456789. The results for Real values are different as the accuracy of the data type is lower.

Style Code	Style	Example
0	Default. Where possible, the result is not presented in scientific notation. Where rounding is required, this style code generates scientific notation values with six digits in the mantissa.	1.23457e+008
1	Always uses scientific notation with eight digits in the mantissa.	1.2345679e+008
2	Always uses scientific notation with sixteen digits in the mantissa.	1.234567891234568e+008

Converting a Money or SmallMoney to a VarChar

You can also apply a style code when converting a Money or SmallMoney value to a string type. The syntax for such a conversion is identical to that of the previous examples but the output is different. The following script demonstrates the available format styles.

DECLARE @money MONEY
SET @money = 123456789.1234

SELECT convert(VARCHAR, @money)     -- 123456789.12
SELECT convert(VARCHAR, @money, 0)  -- 123456789.12
SELECT convert(VARCHAR, @money, 1)  -- 123,456,789.12
SELECT convert(VARCHAR, @money, 2)  -- 123456789.1234

Money and SmallMoney Style Codes

The table below describes the available numeric style codes for use with Money or SmallMoney data. The example values are generated from converting a Money value of 123456789.1234.

Style Code	Style	Example
0	Default. The result is a simple numeric value with no additional formatting. The value is rounded to two decimal places.	123456789.12
1	Commas are included in the value to the left of the decimal point to highlight three-digit groups. The value is rounded to two decimal places.	123,456,789.12
2	The result is a simple numeric value with no additional formatting. The value is has four decimal places.	123456789.1234

14 September 2011