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

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?

Answers


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')

as of SQL Server 2012 you can now do this:

format(@int, '0000#')

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


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

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'

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


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

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))

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')

Need Your Help

What assembly language does C code compile into in Visual Studio?

c visual-studio assembly

When I debug a C project, I can see all the assembly codes it compiles into. I want to know what assembly language that it. Is it NASM or MASM or something else? And if I use inline assembly, will ...

Dynamic char array, size is fixed?

c arrays dynamic

I am trying to create a char array dynamically. However, do not matter which number I put as the desired value of the size array it just keep using 4 has the size. If I place 3,2 or even 8, 10 it d...