Sunday, January 03, 2010

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

1 comment:

Unknown said...

Hi!, i want to do the same thing exactly.but when i run this query it doesnt work.i dont know to use replicate query but saw some articles regarding this and it seems i have to use some thing else also to use replicate query.could u plz help me.

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape