Pad a string with leading zeros so it's 3 char

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

Here's a more general technique for left-padding to any desired width:

declare @x     int     = 123 -- value to be padded
declare @width int     = 25  -- desired width
declare @pad   char(1) = '0' -- pad character

select right_justified = replicate(
                           @pad ,
                           @width-len(convert(varchar(100),@x))
                           )
                       + convert(varchar(100),@x)

However, if you're dealing with negative values, and padding with leading zeroes, neither this, nor other suggested technique will work. You'll get something that looks like this:

00-123

[Probably not what you wanted]

So … you'll have to jump through some additional hoops Here's one approach that will properly format negative numbers:

declare @x     float   = -1.234
declare @width int     = 20
declare @pad   char(1) = '0'

select right_justified = stuff(
         convert(varchar(99),@x) ,                            -- source string (converted from numeric value)
         case when @x < 0 then 2 else 1 end ,                 -- insert position
         0 ,                                                  -- count of characters to remove from source string
         replicate(@pad,@width-len(convert(varchar(99),@x)) ) -- text to be inserted
         )

One should note that the convert() calls should specify an [n]varchar of sufficient length to hold the converted result with truncation.

Answer 2

I had similar problem with integer column as input when I needed fixed sized varchar (or string) output. For instance, 1 to '01', 12 to '12'. This code works:

SELECT RIGHT(CONCAT('00',field::text),2)

If the input is also a column of varchar, you can avoid the casting part.