SDU Tools: LeftPad and RightPad in T-SQL (Right-align, Left-align)

Over the years, I’ve had a surprising number of questions on how to right-align a set of numbers using T-SQL.

The first concept that people seem to miss is that numbers aren’t strings. So there’s no concept of actually aligning a number, it’s only a string representation of a number that can be right-aligned or left-aligned. Or how it’s displayed in a client application.

But if you really want to create a string that has right-aligned numbers, then left padding of the number is what you want.

One of our free SDU Tools is LeftPad. (We also have RightPad).

Here’s an example:

I’ve set NOCOUNT on first (just to clean up the output by removing rowcounts).

I’ve then called LeftPad using the word Hello (the value to be padded), then 10 as the total width, and an o as the padding character.

In the second example, I’ve padded the value 18 with zeroes.

And in the 3rd and 4th examples, I’ve right-aligned some decimal values.

As I mentioned earlier, there’s also a RightPad function, which can be useful for creating fixed width output strings.

You can see them both in action here:

For more information on our free SDU Tools, visit here:

http://sdutools.sqldownunder.com

2017-12-22