SQL Tip: Displaying a Padded Number

Published 18 January 09 08:10 AM | Scott Mitchell

A common requirement for reports is to left or right pad a number so that it conforms to a certain format or so that all the numbers in a column are aligned. Padding a string in VB or C# code is a cinch thanks to the String class's PadLeft and PadRight methods. But what if you need to apply the padding directly in your SQL SELECT query?

Microsoft T-SQL includes an STR function that takes as input a numeric value and returns a string. You can optionally provide a length, which indicates how many characters the resulting string contains, with the numeric value right-aligned. For example, the statement:

PRINT '|' + STR(1, 3) + '|'

Outputs:

| 1|

In other words, the STR function displays the numeric value - 1 - as a string of three characters. Because 1 only requires one character, the first two characters are spaces. If needed, you can use SQL's REPLACE function to replace the spaces with some alternate character. For example, the following left pads the passed in number with 0s.

PRINT REPLACE(STR(1, 3), ' ', '0') -- Outputs 001

In general, you can left pad a number using the following pattern:

REPLACE(STR(numberToPrint, totalLength), ' ', 'characterToLeftPadWith')

And now a challenge for you, my dear reader: Can you come up with a general pattern for a T-SQL statement for right padding a number? The inputs would be numberToPrint, totalLength, and characterToRightPadWith.