My SQL table contains Zipcode’s stored in a numeric format. I have notice that Zipcode with numeric lenght less than 5 has leading zero’s.

Here is few example of zipcode containing leading zero’s

Colombo 12

Nugegoda 10250

Galle 234

I want to display correct zipcode every time im getting value from the database.

So, I want to display zipcode number to always be 5 digits in length. The database might returns a incorrect zipcode, either single digit or up to 4 digits, but I always need enough leading zeros added to the zipcode in order to fit the 5 digit requirement.

xample it should return above zipcode’s in following form (Append Leading Zero)

Colombo 00012

Nugegoda 10250

Galle 00234

Solution

If your column datatype is Varchar than use following

SELECT City,  Right(Replicate(’0′,5) + Zipcode,5)from Location

Or

If your column datatype is Numeric than use following

SELECT City,  Right(Replicate(’0′,5) + convert(varchar(6),Zipcode ) ,5) from Location

Last Modified : January 21st, 2010 Filed under : SQL Server 2008 Navigate : Previous post / Next post