How do I convert an int to a zero padded string in

问题:

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?

回答1:

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

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

回答2:

Another way is:

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

回答3:

as of SQL Server 2012 you can now do this:

format(@int, '0000#')

回答4:

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

回答5:

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

回答6:

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.

回答7:

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.

回答8:

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

回答9:

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'

回答10:

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

回答11:

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

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