Rotating Header Image

Leading Zer0000000000s

So I got an email question asking for a way to pad a column with leading zeros into a 10 digit text/number (the person had an Oracle background and was cursing at the lack of LPAD in SQL Server). There are a few different ways to solve this problem so we're going to look at two. The REPLICATE arguement and the RIGHT arguement.

REPLICATE

DECLARE @counter INT = 0

WHILE @counter < 250000
BEGIN
SELECT
CAST(REPLICATE(0,10 - LEN(@counter)) AS VARCHAR(10)) + CAST(@counter AS VARCHAR(10)) AS 'PaddedZerosColumn'
SELECT @counter += 1000
END

RIGHT

DECLARE @counter INT = 0

WHILE @counter < 250000
BEGIN
SELECT
RIGHT('0000000000' + CAST(@counter AS VARCHAR(10)),10) AS 'PaddedZerosColumn'
SELECT @counter += 1000
END

Now this code was written for SQL Server 2008 and uses Inline Variable Assignment and Compound Operators

Personally I'm fond of the RIGHT solution to this problem as it's much less typing and is easier to read (but both get the job done).

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

3 Comments

  1. Tim Ford says:

    Too Funny, I just submitted a tip to do this at mssqltips.com on Friday night. I created a UDF that will allow you to pad a single character X number of time either at the left, right, center, or evenly-distributed on both left and right.

  2. sweet…. send the link and I’ll add it to the post as the “oober” solution 🙂

    Thanks!

  3. The only difference is the band in all those watches. Twenty watches from $500 to $1100. The band’s the only difference? Well this isn’t the company that Apple was originally, or the company that really changed the world a lot

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.