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”

About Sam Beauvois

Application Developer, .NET enthusiast since 2004, I'm interested in technology watch, usability, code quality, patterns & practices, UX, ...

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>