tsql - Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

Question

I have a string that is up to 3 characters long when it's first created in SQL Server 2008 R2.

I would like to pad it with leading zeros, so if its original value was '1' then the new value would be '001'. Or if its original value was '23' the new value is '023'. Or if its original value is '124' then new value is the same as original value.

I am using SQL Server 2008 R2. How would I do this using T-SQL?

Answer 1

If the field is already a string, this will work

 SELECT RIGHT('000'+ISNULL(field,''),3)

If you want nulls to show as '000'

It might be an integer -- then you would want

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

As required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. eg '0000' and VARCHAR(4)),4