Thursday, June 01, 2006

Padding leading zeros

Find below 3 different ways of adding "Leading" zeros to a given number.

Table Structure

Create table PaddingNumbers
(
Fname varchar(100),
Amount int
)
Go

Populate dummy records

Insert into PaddingNumbers Values ('Vadivel',100)
Insert into PaddingNumbers Values ('Vadi',300)
Go

Method 1:

Select Right('00000' + Convert(varchar, amount), 5) from PaddingNumbers

Method 2:

Select Replace(Str(amount, 5), ' ', '0') from PaddingNumbers

Method 3:

Select Right(Replicate(0, 5) + Convert(varchar(5), amount), 5) from PaddingNumbers

1 comment:

Anonymous said...

Method 1 works quite nicely. Thanks!