REGEXP_LIKE for Postcodes
758537Feb 16 2012 — edited Feb 16 2012Hello 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