SQL Server STR() Function

 In SQL Server, the STR() function is used to convert a numeric expression to a string. The result is a string with a specific length and optionally includes a specified number of decimal places.

Syntax:

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

Parameters:

• float_expression: The numeric value that you want to convert to a string.
• length (optional): The total length of the resulting string. If this value is smaller than the number of characters needed to store the number, SQL Server will return an error.
• decimal (optional): The number of decimal places to include in the string. If omitted, the function rounds to the nearest integer.

Important Points:

1. If the length is specified and the total length of the number (including decimal point and digits) is greater than the specified length, the function will return an error.
2. If the length is less than the length of the number, SQL Server truncates the string.
3. The result will be padded with spaces on the left side if the total length of the number is shorter than the length specified.

Example Usage:

1. Basic Example:

   SELECT STR(123.456, 8, 2) AS Result;
   

   • Converts 123.456 to a string of length 8 with 2 decimal places.
   • Output: ' 123.46' (note the leading space for padding).

2. Without Decimal:

   SELECT STR(123.456, 8) AS Result;
   

   • Converts 123.456 to a string of length 8 without specifying decimal places (defaults to 0).
   • Output: ' 123' (padded with leading spaces).

3. String Length Exceeds the Number of Digits:

   SELECT STR(12345.6789, 10, 2) AS Result;
   

   • Converts 12345.6789 to a string of length 10 with 2 decimal places.
   • Output: '12345.68' (rounded to 2 decimal places).

4. Example with Negative Number:

   SELECT STR(-123.456, 8, 2) AS Result;
   

   • Converts -123.456 to a string of length 8 with 2 decimal places.
   • Output: ' -123.46' (note the space before the minus sign).

Practical Use Case:

If you're working with formatting or displaying numeric data (like financial values) and want to control how the output looks (e.g., number of decimals, total width), STR() is useful. However, for more complex formatting, functions like FORMAT() or CAST() might be more suitable in some cases.

Let me know if you'd like more examples!