Adding Leading Zeroes To A Number

After having successfully survived the festivities, here I am again, ready for another year of blogging, article writing, forum answering, conference attending, …

Happy New Year to anyone reading this!!

In this year’s first post I’d like to share my preferred way of adding leading zeroes to a number, useful for any situation where you need to convert a number to a string using SQL.  In fact, this method can be used whenever a certain string needs to get increased to a specific length using a particular character, not just numbers.

And here’s the code sample:

declare @number int = 42;
declare @character char(1) = '0';
declare @expectedLength int = 8;

select REPLICATE(@character, @expectedLength - LEN(@number)) + CAST(@number as varchar(8)) as Result;

I’m using the REPLICATE() function to create a string of our leading character, in this case a zero.  The length of this string of zeroes is the expected length minus the length of the actual number.  The LEN() function is used to get the length of our number – note that LEN() expects a string as parameter so an implicit cast from int to varchar is being performed here.

Then I use the + operator to concatenate the string of zeroes with our actual number.  This number gets converted to varchar using the CAST() function.  The length of the varchar should be equal to the maximum length that our output string can be.  This way we ensure that all acceptable values are being converted to string.  In the case that our number is longer than the expected output string, the result will be NULL.

And here’s the result:

Have fun in 2010!