Zeros and strings manipulation in Tsql
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”
Application Developer, .NET enthusiast since 2004, I'm interested in technology watch, usability, code quality, patterns & practices.
