T-SQL: STR Function
Excerpt by Don Kiely
The STR function is another way to perform a specific conversion: It returns a string from a numeric expression:
STR(float_expression[, length[, decimal]])
The optional length and decimal parameters offer more flexibility than CAST or CONVERT when converting decimal data to character data, which enables explicit control over formatting. The following query uses STR to create a character string that is six characters long (with padding at the beginning of the string), and one decimal place:
SELECT UnitPrice, STR(UnitPrice, 6, 1) AS Formatted
ORDER BY UnitPrice DESC;
The Figure below shows the first few rows of the output in a text window, where you can see that the numbers have leading spaces to align them on the right side.
Needing to handle data conversion issues isn't anything new or all that different in T-SQL compared to other programming languages that use strong typing. For the most part, you should avoid relying on T-SQL for implicit conversions. This will help you avoid troublesome bugs and make your code more readable, definitely a win-win situation!
This course excerpt was originally posted March 29, 2013 from the online courseware SQL Server 2012, Part 7 of 9: Introduction to T-SQL by Don Kiely