Sunday, January 03, 2010
Append Leading Zero to return value of SQL Select Statement.
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
If your column datatype is Numeric than use following
SELECT City, State, Right(Replicate('0',5) + convert(varchar(6),Zipcode ) ,5) from Location