How do I convert an int to a zero padded string in T-SQL?

Question

Let's say I have an int with the value of 1. How can I convert that int to a zero padded string, such as '00000001'?

Answer 1

Another way is:

DECLARE @iVal int = 1
select REPLACE(STR(@iVal, 8, 0), ' ', '0')

Answer 2

Declare @MyInt integer Set @MyInt = 123
Declare @StrLen TinyInt Set @StrLen = 8

Select Replace(Str(@MyInt, @StrLen), ' ' , '0')

Answer 3

Or if you really want to go hard-core... ;-)

declare @int int
set @int = 1

declare @string varchar(max)
set @string = cast(@int as varchar(max))

declare @length int
set @length = len(@string)

declare @MAX int
set @MAX = 8

if @length < @MAX
begin
    declare @zeros varchar(8)
    set @zeros = ''

    declare @counter int
    set @counter = 0

    while (@counter < (@MAX - @length))
    begin
     set @zeros = @zeros + '0'
     set @counter = @counter + 1
    end
    set @string = @zeros + @string
end
print @string

Answer 4

If I'm trying to pad to a specific total length, I use the REPLICATE and DATALENGTH functions, like so:

DECLARE @INT INT
DECLARE @UNPADDED VARCHAR(3)
DECLARE @PADDED VARCHAR(3)

SET @INT = 2
SET @UNPADDED = CONVERT(VARCHAR(3),@INT)
SET @PADDED = REPLICATE('0', 3 - DATALENGTH(@UNPADDED)) + @UNPADDED
SELECT @INT, @UNPADDED, @PADDED

I used variables here for simplicity, but you see, you can specify the final length of the total string and not worry about the size of the INT that you start with as long as it's <= the final string length.

Answer 5

I always use:

SET @padded = RIGHT('z0000000000000'
  + convert(varchar(30), @myInt), 8)

The z stops SQL from implicitly coverting the string into an int for the addition/concatenation.

Answer 6

If the int can go negative you have a problem, so to get around this I sometimes do this:

DECLARE @iVal int 
set @iVal = -1
    select 
        case 
            when @ival >= 0 then right(replicate('0',8) + cast(@ival as nvarchar(8)),8)
            else '-' + right(replicate('0',8) + cast(@ival*-1 as nvarchar(8)),8)
        end

Answer 7

This work for me:

SELECT RIGHT('000' + CAST(Table.Field AS VARCHAR(3)),3) FROM Table