Search code examples
sql-servert-sql

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

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?

Solution

  • 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