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″?

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

result is “00000001″

And the inverse :

Let’s say you have a string “0000000120345FER” and you want to remove the leading zeros

DECLARE @iVal nvarchar(max)
set @iVal = '0000000120345FER'

DECLARE @iVal2 nvarchar(max)
set @iVal2= SUBSTRING(@iVal, PATINDEX('%[^0]%', @iVal+'.'), LEN(@iVal))

print @iVal2

result is “120345FER”

Tweet