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

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

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

Answer 2

as of SQL Server 2012 you can now do this:

format(@int, '0000#')

Answer 3

Another way is:

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

Answer 4

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

Answer 5

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

Answer 6

And then there's this one, using REPLICATE:

SELECT REPLICATE('0', 7) + '1'

Of course, you can replace the literals 7 and '1' with appropriate functions as needed; the above gives you your example. For example:

SELECT REPLICATE('0', 8 - LEN(CONVERT(nvarchar, @myInt))) + CONVERT(nvarchar, @myInt)

will pad an integer of less than 8 places with zeros up to 8 characters.

Now, a negative number in the second argument of REPLICATE will return NULL. So, if that's a possibility (say, @myInt could be over 100 million in the above example), then you can use COALESCE to return the number without leading zeros if there are more than 8 characters:

SELECT COALESCE(REPLICATE('0', 8 - LEN(CONVERT(nvarchar, @myInt))) + CONVERT(nvarchar, @myInt), CONVERT(nvarchar, @myInt))

Answer 7

Very straight forward way to think about padding with '0's is, if you fixed your @_int's to have 4 decimals, you inject 4 '0's:

    select RIGHT( '0000'+ Convert(varchar, @_int), 4) as txtnum

; if your fixed space is 3, you inject 3'0's

    select RIGHT( '000'+ Convert(varchar, @_int), 3) as txtnum

; below I inject '00' to generate 99 labels for each bldg

declare @_int int
set @_int = 1
while @_int < 100 Begin
    select BldgName + '.Floor_' + RIGHT( '00'+ Convert(varchar, @_int), 2) 
    + '.balcony' from dbo.tbl_FloorInfo group by BldgName
    set @_int = @_int +1
End

Result is:

    'BldgA.Floor_01.balcony'
    'BldgB.Floor_01.balcony'
    'BldgC.Floor_01.balcony'
     ..
     ..
    'BldgA.Floor_10.balcony'
    'BldgB.Floor_10.balcony'
    'BldgC.Floor_10.balcony'
     ..
     ..
     ..
    'BldgA.Floor_99.balcony'
    'BldgB.Floor_99.balcony'
    'BldgC.Floor_99.balcony'

Answer 8

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 9

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 10

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 11

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 12

I think Charles Bretana's answer is the simplest and fastest. A similar solution without using STR is:

SELECT REPLACE(REVERSE(
        CONVERT(CHAR(5 /*<= Target length*/)
                , REVERSE(CONVERT(VARCHAR(100), @MyInt)))
     ), ' ', '0')