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, NameCounterfrom`

` 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.

Perfect!! Thank you!

ReplyDelete