When you output a series of numbers in T-SQL, people often want to right-align the numbers. So instead of numbers that look like this:
They want an output that looks like this:
Now the first thing to understand is that this is generally a client-tool or program issue, not a T-SQL issue. I ran these queries in SQL Server Management Studio and it could just as easily have decided to show me numbers right-aligned.
The only way that T-SQL can send you a right-aligned value is to send you a string value instead.
In the March 2017 update to SDU_Tools, we added a LeftPad function that can do this. SDU Tools is a free library for SQL Server developers and DBAs. You can easily just add it to your database, then call the functions as needed. The functions get added in a separate schema and are easy to remove later if you ever wish to. (We hope you won’t want to). They’re also easy to upgrade. You’ll find info here: http://www.sqldownunder.com/resources/sdutools
Let’s look at the output from the function:
The parameters to the function are the value to be padded (which doesn’t have to be a number), the total number of characters, and the character to use for the padding.
Similarly, there is a RightPad function that can be used like this:
We hope you’ll find these functions useful. More details are here: