A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Thursday, September 10, 2009

Padding Numbers with Leading Zeroes

In some contexts a number needs to be padded in order to look right or to work in the desired context. For example, in the Shelby v5 software, the Company Number and Fund Number values are always displayed as three-digit or four-digit values, with padded zeroes for one- and two-digit vlaues. Thus, company "1" is displayed as either "001" or "0001."

Another example from Shelby v5 is when the NameCounter is used to create the path for an individual picture. Pictures are stored with a file name that includes seven digits for the NameCounter value, so the number is padded with however many zeroes are needed to make seven digits.

To show you how each of these situations can be handled, I will do some sample queries on the Shelby v5 table called CNHst. This table contains contribution history, but that is incidental to this exercise. I am using this table because it contains both a CoNu (company number) column and a NameCounter column.

To start with, here is a basic query to pull the values we eventually want to pad with leading zeroes. An example result set appears underneath.

select
CoNu,
NameCounter
from
Shelby.CNHst









The original values are numeric, and therefore they will always show up with only the digits in the number itself. We cannot "pad" numeric values, so the first step is to change the numeric values into character values, using the CAST() function.

select
CoNu = cast(CoNu as varchar(4)),
NameCounter = cast(NameCounter as varchar(7))
from
Shelby.CNHst









The only visible change is that the values are now aligned to the left of each cell instead of aligned to the right. However, now that the values have been changed into character values, we can add zeroes to the left by a simple concatenation of a literal text string of zeroes.

select
CoNu = '000' + cast(CoNu as varchar(4)),
NameCounter = '000000' + cast(NameCounter as varchar(7))
from
Shelby.CNHst









To make each row the same fixed length of four digits (for CoNu) and seven digits (for NameCounter), we need to cut off the extra zeroes. The best way to do this in T-SQL is to use the RIGHT() function to pick up the string of characters starting with the rightmost character and counting left the desired number of digits.

select
CoNu = right('000' + cast(CoNu as varchar(4)), 4),
NameCounter = right('000000' + cast(NameCounter as varchar(7)), 7)
from
Shelby.CNHst









Now we have uniform output with each number reflected as desired with a fixed number of digits and padded zeroes as needed.

1 comment:

Followers