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!

REGEXP_LIKE for Postcodes

758537Feb 16 2012 — edited Feb 16 2012
Hello Experts

I've got the following query to check if the value entered in the field is a valid postcode or not :-
SELECT 'x'
FROM DUAL
WHERE REGEXP_LIKE(REPLACE(UPPER(CP_ADDRESS_LINE6), ' '),
'[[:alpha:]]{1,2}[0-9]{1,3}[[:alpha:]]{2}' ||
'|[[:alpha:]]{1}[0-9]{1}[[:alpha:]]{1}[0-9]{1}[[:alpha:]]{2}');

I would expect this query not to return anything if a post code such as "MOO 2UP". As my REGEXP_LIKE says I'm looking for 2 characters ([[:alpha:]]{1,2}) followed by 1 to 3 digits ([0-9]{1,3}) and then followed by 2 characters ([[:alpha:]]{2}) OR the pattern is 1 character ([[:alpha:]]{1}) followed by one digit ([0-9]{1}) followed by 1 character ([[:alpha:]]{1}) followed by 1 digit ([0-9]{1}) and finally followed by 2 characters ([[:alpha:]]{2})

But this pattern doesn't work as I explained above. If the postcode such as POO 2SS is entered it comes up as a valid postcode while as per the query above I'm expected 2 characters to be followed by a digit, which is not the case with POO 2SS.

Can anyone please advice where I'm going wrong.

Thank you
This post has been answered by Paul Horth on Feb 16 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 15 2012
Added on Feb 16 2012
14 comments
2,384 views