Right-aligning numbers in T-SQL

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.

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.

You can find out more about our free SDU Tools here:

http://sqldownunder.com/sdu-tools

2017-04-09