Append Leading Zero to return value of SQL Select Statement

Append Leading Zero to return value of SQL Select Statement.

Scenario

My SQL table contains USA 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

Rochester, MI 4

Aguadilla PR 603

Caguas PR 726

Ponce PR 731

Belmont MA 2478

Andover NJ 7821

Metuchen NJ 8840

Vernon CT 6066

I want to display correct zipcode every time i fetch 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.

Example it should return above zipcode's in following form (Append Leading Zero)

Rochester, MI 00004

Aguadilla PR 00603

Caguas PR 00726

Ponce PR 00731

Belmont MA 02478

Andover NJ 07821

Metuchen NJ 08840

Vernon CT 06066

Solution for displaying leading zero while returning values from SQL Select statement

If your column datatype is Varchar than use following

SELECT City, State, Right(Replicate('0',5) + Zipcode,5) from Location

Or

If your column datatype is Numeric than use following

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