Returns the character equivalent of the specified number, and pads the output with a character or numeric to the specified length.
str(approx_numeric[, length [, decimal]])
is any approximate numeric (float, real, or double precision) column name, variable, or constant expression.
sets the number of characters to be returned (including the decimal point, all digits to the right and left of the decimal point, and blanks). The default is 10.
sets the number of decimal digits to be returned. The default is 0. Also can be used to pad the output with a character or numeric to the specified length.
When you specify a character or numeric as a literal string, the character or numeric is used as padding for the field. When you specify a numeric value, sets the number of decimal places. The default is 0. When decimal is not set, the field is padded with blanks to the value specified by length.
When decimal is set as the string literal '0', the field is padded with 0 to a length of 10 spaces.
select str(5,10,'0')
---------------- 0000000005
When decimal is a numeric of 5, the number of decimal places is set to 5.
select str(5,10,5)
---------------- 5.00000
When decimal is set to the character of '_', the original value is maintained and the field is padded with the specified character to a length of 16 spaces.
select str(12.34500,16,'_')
---------------- ________12.34500
Without decimal set, the floating number is set to zero decimal places and the field is padded with blanks to a length of 16 spaces.
select str(12.34500e,16)
---------------- 12
With decimal set to a numeric, the floating number is processed to 7 decimal places and the field is padded with blanks to a length of 16 spaces.
select str(12.34500e,16,7)
---------------- 12.3450000
Specify a prefix character and process a floating number to a specified number of decimal places using these examples:
select str(convert(numeric(10,2),12.34500e),16,'-')
---------------- -----------12.35
select str(convert(numeric(10,8),12.34500e),16,'-')
---------------- -----12.34500000
length and decimal are optional, but if used, must be positive integers. str rounds the decimal portion of the number so that the results fit within the specified length. The length should be long enough to accommodate the decimal point and, if the number is negative, the number’s sign. The decimal portion of the result is rounded to fit within the specified length. If the integer portion of the number does not fit within the length, however, str returns a row of asterisks of the specified length. For example:
select str(123.456, 2, 4)
-- **
If approx_numeric is NULL, returns NULL.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute str.