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!!