SQL Server Numeric Formatting

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:

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.