How to format Zipcode / Postal Code in Oracle

Sometimes , in the applications , zipcodes are stored as numbers . That means , the leading zeros for some of the postal codes will not be displayed in the applications / reports. This may not be desirable to the end user.

As an example , in New Jersey , all the zip codes starts with leading zero. But , if you look at the database , the leading zero will be missing .

SQL> SELECT *
 2 FROM
 3 (SELECT u.*
 4 FROM uszipcode u
 5 WHERE state = ‘NJ’
 6 )
 7 WHERE rownum < 5;

POSTALCODE CITY ST
 - - - - - - - - - - - - - - - - - - - - - - - - - - 
 7001 Avenel NJ
 7002 Bayonne NJ
 7003 Bloomfield NJ
 7004 Fairfield NJ

To format the postal code , you may want to use the following SQL.

SQL> SELECT *
 2 FROM
 3 (SELECT u.* ,
 4 TO_CHAR(postalcode ,’fm00000') formatted_zipcode
 5 FROM uszipcode u
 6 WHERE state = ‘NJ’
 7 )
 8 WHERE rownum < 5;

POSTALCODE CITY         ST      FORMAT
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
 7001      Avenel        NJ     07001
 7002      Bayonne       NJ     07002
 7003      Bloomfield    NJ     07003
 7004      Fairfield     NJ     07004

Originally published at https://zahirmohideen.blogspot.com/2012/02/how-to-format-zipcode-postal-code-in.html