sql - Prepend 0s to string (to specific length) -

this question has answer here:

• formatting numbers padding leading zeros in sql server 10 answers

i have query i'm pulling in check number database. return value needs 9 characters exactly. of check numbers 6 characters, want prepend 0s to string ensure 9 characters. since check numbers can vary in length, different number of 0s each one. what's efficient way this?

if they're between 6 , 9 digits, easiest (and best) way go using case.

select case when len(check_num)= 6 '000' when len(check_num) = 7 '00'         when         len(check_num) = 8 '0' else '' end + check_num    table  

edit
in case original values numbers (int), try this:

select case when len(check_num)= 6 '000' when len(check_num) = 7 '00'         when         len(check_num) = 8 '0' else '' end + cast(check_num varchar(9))    table1  

take @ sql fiddle.