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


SQL>


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