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.
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.
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>
WHERE TRANSLATE ( code_zip , '012345678BCDEFGHIJKLMNOPQRSTUVWXYZ' , '999999999AAAAAAAAAAAAAAAAAAAAAAAAA' ) = 'A9A 9A9'
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