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”