SQL Server - Formatting Numbers as Strings with Leading Zero’s

Sometimes it’s necessary to format numbers as Strings with leading zero’s.

One such example is when we sort a list in Excel, Excel will sort the numbers as 0,1,10,2,20,3,30,…..

So, to get round this, we simply append a leading 0 to the number.

This is achieved in T-SQL by using the RIGHT instruction with following syntax.

RIGHT('00'+ CONVERT(VARCHAR,YourNumber),2)

The first Parameter - 00, is the default format for your formatted number, and should contain the number of zero’s corresponding to the total length of your formatted string.

The second parameter -‘YourNumber’, is the name of the field you wish to convert.

The third parameter - 2, is the total length of your formatted field.