Using STR

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 either 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.

The following 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 converted to a character string exceeds the length specified in STR, STR returns ** for the specified length. In the following 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, asterisks are returned. For example, consider the batch:

SELECT STR(1234567.89, 7, 2);
SELECT STR(1234567.89, 6, 2);

Here is the result set.

-------
1234568

(1 row(s) affected)
------

****

(1 row(s) affected)

STR offers more flexibility than CAST when decimal data types are converted to characters, because it gives explicit control over formatting.