STR (TSQL Function)
Returns a float
value formatted to string.
Syntax
STR ( float_expression [, length [, decimal]] )
Arguments
- float_expression
- is an expression of type
float
. (see Important note in Remarks) - length
- is the total length of the result string. It is of type
int
. The default is 10.
This includes the sign, digits, decimal point and spaces. - decimal
- is the number of digits to the right of the decimal point. It is of type
int
. The default is 0.
decimal must be <= 16. Else, it is changed to 16.
Return Types
Returns varchar
.
Remarks
The result string is right-justified.
If length is too short to display the integer part of the number, ** is output instead.
If length <= 0, result is NULL.
If decimal < 0, result is NULL.
IMPORTANT
This function is really to formatfloat
values, notmoney
ornumeric
. If you pass one of these types as argument toSTR
, it will be cast tofloat
, and some loss of precision can occur.
For all these types, it is better to use theFORMAT
function.
Examples
DECLARE @a FLOAT = 516.5234567890123456789
PRINT '#' + STR(@a) + '#'; -- default length is 10
PRINT '#' + STR(@a, 1) + '#'; -- length too small, '*' is printed
PRINT '#' + STR(@a, 2) + '#'; -- length too small, '**' is printed
PRINT '#' + STR(@a, 3) + '#';
PRINT '#' + STR(@a, 4) + '#';
PRINT '#' + STR(@a, 16) + '#';
The result is:
# 517#
#*#
#**#
#517#
# 517#
# 517#
Examples with decimal argument
DECLARE @a FLOAT = 516.5234567890123456789
PRINT '#' + STR(@a, 10, 0) + '#';
PRINT '#' + STR(@a, 10, 1) + '#';
PRINT '#' + STR(@a, 10, 2) + '#';
PRINT '#' + STR(@a, 10, 3) + '#';
PRINT '#' + STR(@a, 10, 4) + '#';
PRINT '#' + STR(@a, 10, 5) + '#';
PRINT '#' + STR(@a, 10, 6) + '#'; -- prints 516.523457
PRINT '#' + STR(@a, 10, 7) + '#'; -- decimal too large, prints 516.523457 too
The result is:
# 517#
# 516.5#
# 516.52#
# 516.523#
# 516.5235#
# 516.52346#
#516.523457#
#516.523457#
Examples with decimal > 16
DECLARE @a FLOAT = 516.5234567890123456789
PRINT '#' + STR(@a, 25, 14) + '#';
PRINT '#' + STR(@a, 25, 15) + '#';
PRINT '#' + STR(@a, 25, 16) + '#'; -- decimal max value is 16
PRINT '#' + STR(@a, 25, 17) + '#'; -- decimal is changed to 16
The result is:
# 516.52345678901236#
# 516.523456789012357#
# 516.5234567890123571#
# 516.5234567890123571#