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

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.

Using STR to convert a number to a string

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!

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.

Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

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