Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Regular Expression for US & Canada Zip code

Santhosh Tirunahari-OracleNov 19 2008 — edited Nov 20 2008
select zipcode from zipcode_hr where REGEXP_LIKE(zipcode, '([[:digit:]]{5})(-[[:digit:]]{4})?$')

I am trying to find out the US zip code format from the regular expression above is the query ...

US Zip code format : either 5 digit number or 5 digitnumber - 4 digit number ( EX: 12345 or 12345-6789 )

above expression is giving results like this ( 12345-6789 ) and with five digit number too. and the problem is with five digit numbers results contains some 6 digit and 7 digit numbers.

Can any one provide help on this.

Ref Link : http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html

And if possible can any one give the Reg Ex for Canada Zip code.

Thanks in Advance.
Santhosh
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 18 2008
Added on Nov 19 2008
3 comments
5,394 views