T-SQL: STR Function

The STR function is another way to perform a specific conversion: It returns a string from a numeric expression:

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:

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 is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *