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

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

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

Another way is:

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

This work for me:

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

...

I created this user function

T-SQL Code :

CREATE FUNCTION CIntToChar(@intVal Int, @intLen Int) RETURNS nvarchar(24) AS BEGIN

IF @intlen > 24
   SET @intlen = 24

RETURN REPLICATE('0',@intLen-LEN(RTRIM(CONVERT(nvarchar(24),@intVal)))) 
    + CONVERT(nvarchar(24),@intVal) END

Example :

SELECT dbo.CIntToChar( 867, 6 ) AS COD_ID

OUTPUT

000867

Use FORMAT(<your number>,'00000000') use as many zeroes as you need to have digits in your final outcome.

Here is official documentation of the FORMAT function

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.

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.

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

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