The STR function converts numbers to characters, with optional parameters for specifying the total length of the result, including the decimal point and the number of places after the decimal point.
Length and decimal parameters to STR (if supplied) should be positive. The default length is 10. The number is rounded to an integer by default or if the decimal parameter is 0. The specified length should be greater than or equal to the part of the number before the decimal point plus the number sign (if any):
This example converts the float expression of 123.45 to a character with a length of 6 characters and 2 decimal places.
SELECT STR(123.45, 6, 2)
Here is the result set:
-----
123.45
(1 row(s) affected)
If the integer part of the expression being converted to a character string exceeds the length specified in STR, STR returns ** for the specified length. For example, the number 1234567.89 has 7 digits to the left of the decimal point. If the length parameter on STR is 7 or more, the resulting string contains the integer and as many of the decimals as will fit. If the length parameter in STR is 6 or less, then asterisks are returned for example, the batch:
SELECT STR(1234567.89, 7, 2)
SELECT STR(1234567.89, 6, 2)
Returns:
-------
1234568
(1 row(s) affected)
------
******
(1 row(s) affected)
STR offers more flexibility than CAST when converting decimal data types to characters because it gives explicit control over formatting.