DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Padding A String With Leading Zeros In Sql Server

07.28.2009
| 7218 views |
  • submit to reddit
        
declare @number tinyint

set @number = 2

select case when len( @number ) = 1 then '0' else '' end + cast( @number as varchar(2) )

</CODE>
For a number that will be greater than two characters in length you can do this...
<CODE>

declare @number int
declare @string varchar(10)
declare @size_of_fixed_string tinyint

set @size_of_fixed_string = 10
set @number = 40

print replicate( '0', @size_of_fixed_string )

set @string = left( replicate( '0', @size_of_fixed_string ), @size_of_fixed_string - len( @number ) ) + cast( @number as varchar(10) )

print @string