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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Frank Kulash Nov 19 2008 — edited on Nov 20 2008
Hi,

Did you forget to anchor the beginning of the pattern?
REGEXP_LIKE('1234567', '([[:digit:]]{5})(-[[:digit:]]{4})?$') 
returns TRUE because the substring '34567' matches the pattern of exactly five digits, followed immediately be the end-of-line ($).
You didn't say anything about what comes before the five digits, such as the beginning of the line (^).
REGEXP_LIKE('1234567', '^([[:digit:]]{5})(-[[:digit:]]{4})?$') 
returns FALSE.
Nuerni Nov 20 2008
Have a look at http://www.geonames.org in the download-section where you'll find useful files containing postal-code-regex and much more country-related informations...
Below are the right examples to find out the US and canada ZIP code patterns

US - (REGEXP_LIKE(zipcode,'^[[:digit:]]{5} [-/.] [[:digit:]]{4}$') or REGEXP_LIKE(zipcode, '^[0-9]{5}$' ))


Canada - REGEXP_LIKE(zipcode,'^[[:alpha:]]{1}[[:digit:]]{1}[[:alpha:]]{1}[ |-][[:digit:]]{1}[[:alpha:]]{1}[[:digit:]]{1}$')
1 - 3
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,122 views