THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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:

image

They want an output that looks like this:

image

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:

image

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:

image

We hope you’ll find these functions useful. More details are here:

http://www.sqldownunder.com/resources/sdutools

Published Sunday, April 9, 2017 3:24 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement