LearnNowOnline

Blog

T-SQL: STR Function

Excerpt by Don Kiely | March 29, 2013

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

FROM dbo.Products

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 post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.