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 the new value is the same as the original value.

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

Answer 1

If the field has already a string, then the below code should work:

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

If you want nulls to show as '000'. Then it will be considered as an integer –Therefore, you should try below code:

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

The above solution will work only if the length <= 3.

But, if you want something larger then you need to change the string constant and the two integer constants to the width that you need to do this:

For example: ‘00000' and VARCHAR(5)),5