In SQL Server, the T-SQL STR() function returns character data converted from numeric data. The return value has a return type of varchar.

You provide the numeric data as an argument when calling the function. The function then converts it to character data. There are also two optional arguments that you can use to specify the length, and the number of places to the right of the decimal point.

Syntax

The syntax goes like this:

STR ( float_expression [ , length [ , decimal ] ] )

Where float_expression is an expression of approximate numeric (float) data type with a decimal point.

length is an optional argument you can use to specify the total length. This includes decimal point, sign, digits, and spaces. The default is 10.

decimal is the number of places to the right of the decimal point. This can be a maximum value of 16. If more than that, it’s truncated to 16.

Example 1 – Basic Usage

Here’s an example of how it works:

SELECT STR(123.45) AS Result;

Result:

+------------+
| Result     |
|------------|
|        123 |
+------------+

Example 2 – Rounding

By default, the number is rounded to an integer. If we adjust the input from the previous example (eg, increase the fractional part from .45 to .55) we get the following:

SELECT STR(123.55) AS Result;

Result:

+------------+
| Result     |
|------------|
|        124 |
+------------+

Example 3 – Length

When using the optional length parameter, its value should be greater than or equal to the part of the number before the decimal point plus the number’s sign (if any).

For example, the following is fine, because length is greater than the part of the number before the decimal point:

SELECT STR(123.55, 4) AS Result;

Result:

+----------+
| Result   |
|----------|
|  124     |
+----------+

However, the following example demonstrates what happens when the length value is too small:

SELECT STR(123.55, 2) AS Result;

Result:

+----------+
| Result   |
|----------|
| **       |
+----------+

Also note that length determines the length of the output, and includes the decimal point, sign, digits, and spaces. The default length is 10.

Here’s an example to demonstrate the difference in output depending on the value of the length argument:

SELECT 
  123 AS '123',
  STR(123, 3) AS '3',
  STR(123, 10) AS '10',
  STR(123, 16) AS '16';

Result:

+-------+-----+------------+------------------+
| 123   | 3   | 10         | 16               |
|-------+-----+------------+------------------|
| 123   | 123 |        123 |              123 |
+-------+-----+------------+------------------+

For another way to demonstrate this, in the following example I trim the output using the TRIM() function (and compare it to the untrimmed version):

SELECT 
  TRIM(STR(123, 16)) AS 'A',
  STR(123, 16) AS 'B';

Result:

+-----+------------------+
| A   | B                |
|-----+------------------|
| 123 |              123 |
+-----+------------------+

Example 4 – Decimal Places

Here’s an example of using the optional decimal parameter:

SELECT STR(123.55, 8, 4) AS Result;

Result:

+----------+
| Result   |
|----------|
| 123.5500 |
+----------+

Notice that I increased the value for length in order to accommodate the decimal places.

If I reduce the length value to 6, I get this:

SELECT STR(123.55, 6, 4) AS Result;

Result:

+----------+
| Result   |
|----------|
| 123.55   |
+----------+