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?

Solution 1:

Declare@MyIntintegerSet@MyInt=123Declare@StrLen TinyInt Set@StrLen=8Select Replace(Str(@MyInt, @StrLen), ' ' , '0')

Solution 2:

Another way is:

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

Solution 3:

as of SQL Server 2012 you can now do this:

format(@int, '0000#')

Solution 4:

This work for me:

SELECTRIGHT('000'+CAST(Table.Field ASVARCHAR(3)),3) FROMTable

...

I created this user function

T-SQL Code :

CREATEFUNCTION CIntToChar(@intValInt, @intLenInt) RETURNS nvarchar(24) ASBEGIN

IF @intlen>24SET@intlen=24RETURN 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

Solution 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