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.

Identify canadian zip codes

570672Feb 4 2009 — edited Mar 18 2010
Answered

Edited by: user567669 on Mar 18, 2010 9:07 AM

Comments

414042 Feb 4 2009
From Wikipedia: A Canadian postal code is a string of six characters in the format X#X #X#, where X is a letter and "#" is a single digit, with a space separating the third and fourth characters. An example is K1A 0B1, which is for Canada Post's Ottawa headquarters.

Greg
Tubby Feb 4 2009

This should work for you.

ME_XE?select * from (select 'v6b1r1' as postal_code from dual) where length(postal_code) / 2 = length(regexp_replace(postal_code, '[0-9]' ));

POSTAL_CODE
------------------
v6b1r1

1 row selected.
570672 Feb 4 2009
Thanks for your help. But I get the following error:

ORA-00904: "REGEXP_REPLACE": invalid identifier

What am I doing wrong?
666352 Feb 4 2009
SQL> WITH t AS
  2       (SELECT 'H4T 1E8' AS code_zip
  3          FROM DUAL
  4        UNION ALL
  5        SELECT 'HiT 1E8' AS code_zip
  6          FROM DUAL)
  7  SELECT *
  8    FROM t
  9   WHERE REGEXP_LIKE (code_zip, '^([A-Z]\d[A-Z]\s\d[A-Z]\d)$');

CODE_ZI
-------
H4T 1E8

SQL> 
Tubby Feb 4 2009
What Oracle version are you on?
570672 Feb 4 2009
Oracle 9.2.0.1.0
Satyaki_De Feb 4 2009
Regexp available from 10g.

Regards.

Satyaki De.
Frank Kulash Feb 4 2009 — edited on Feb 4 2009
Hi,

If you're using Oracle 9 (or earlier)
WHERE   TRANSLATE ( code_zip
                  , '012345678BCDEFGHIJKLMNOPQRSTUVWXYZ'
                  , '999999999AAAAAAAAAAAAAAAAAAAAAAAAA'
                  ) = 'A9A 9A9'
By the way, Santa Claus has his own postal code: 'H0H 0H0'.
Satyaki_De Feb 4 2009
By the way, Santa Claus has his own postal code: 'H0H 0H0'.
Interesting.... ;)

Regards.

Satyaki De.
570672 Feb 4 2009 — edited on Feb 4 2009
Thanks

Edited by: user567669 on Feb 4, 2009 10:58 AM
MichaelS Feb 4 2009
Or Salim Chelabi's 9i equivalent
SQL>  with t as (select 'H4T 1E8' as code_zip from dual union all
           select 'HiT 1E8' as code_zip from dual)
select *
  from t
 where owa_pattern.amatch (code_zip, 1, '([A-Z]\d[A-Z]\s\d[A-Z]\d)$') > 0

CODE_ZI
-------
H4T 1E8
1 - 11
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 4 2009
Added on Feb 4 2009
11 comments
1,181 views