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 format float values, not money or numeric. If you pass one of these types as argument to STR, it will be cast to float, and some loss of precision can occur.
For all these types, it is better to use the FORMAT 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#