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.