Jordan, you are thinking procedurally here too.

SQL stores values. Don't worry about formatting until you DISPLAY those values.

Check this out:

declare @sample int

set @sample = 123

select @sample

select str(@sample)

The STR() function right adjusts, and can decimal-align a numeric value. But we store it simply as an integer for efficiency's sake. Jeff Moden would tell you to not even bother with the STR() function. Just pass the value back to the calling application and let IT handle the formatting.

Some people will tell you I'm already crazy. So, yes, I am asking you why you think you need to store a left padded number in a varchar(10). Why waste time, space, and effort tossing in useless blanks?