Thursday 1 October 2015

Pad number with leading zeros sql server up to 4 digit

Many of times when we generate invoice number or registration id or specific id with formatted text. We need to pad with leading zeros. Here is simple one line syntax to get formatted text in SQL server.

Code:
       

declare @number int =  1;
print right('0000' + cast(@number as varchar(4)) , 4)
print right('0000' + convert(varchar(4), @number) , 4)
print right(replicate('0',4) + convert(varchar(4), @number) , 4)
print  cast(replace(str(@number,4),' ','0')as char(4))
print format(@number,'0000')

Output:
0001
0001
0001
0001