Use REGEXP to help clean up POSTCODE field

Hi

I need to find invalid postcodes using REGEXP.

The valid formats are:

A9 9AA
AA99 9AA
AA9 9AA
A9A 9AA
AA9A 9AA


SimpleIntelligenceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul_Harris_FusionCommented:
Me again.

REGEXP_LIKE (postcodecolumn, '[a-z]{1,2}[0-9]{1,2}[a-z]{0,1} [0-9][a-z]{2} , 'i' )
i.e. 1 or 2 letters followed by 1 or 2 numbers followed by 0 or 1 letters followed by a space followed by exactly 1 number followed by exactly 2 letters

This should return true for valid postcodes, false otherwise
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SimpleIntelligenceAuthor Commented:
Cheers Paul. Will try it after a coffee.
0
SimpleIntelligenceAuthor Commented:
SELECT *--(premise.*)
FROM
        (SELECT *
         FROM ice1.premise
         UNION ALL
         SELECT *
         FROM ice2.premise) premise    
       where NOT REGEXP_LIKE(postcode, '[a-z]{1,2}[a-z]{0,1}[0-9]{1,2} [0-9][a-z]{2} , i' )



I don't want it to return a postcode like

KW15LE

but that query above does. Any thoughts?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

VishnukumarCommented:
This regex accepts AA99A 9AA. So the regular expression may be like this [a-z]{1,2}[0-9]([0-9]|[a-z]){0,1} [0-9][a-z]{2}
0
SimpleIntelligenceAuthor Commented:
Hi Vishnu

Thanks for looking at this question. In order for me to understand this function, can you show me the expression that would identify:

KW1 5LE
0
VishnukumarCommented:
// can you show me the expression that would identify

sorry i can't get the question.... which expression you need.

 
0
SimpleIntelligenceAuthor Commented:
I want the postcode KW1 5LE to be picked up by the REGEXP function.
0
VishnukumarCommented:
[A-Z]{2}[0-9]  [0-9][A-Z]{2}
0
SimpleIntelligenceAuthor Commented:
Its still not picking up

KW1 5LE
0
VishnukumarCommented:

Could you please send your query that you have executed...

please change the [A-Z] as [[:alpha:]]. This accepts both lower and upper case. please put ^ in the beging of the regular exp and $ for last.
'^[[:alpha:]]...  $'
0
SimpleIntelligenceAuthor Commented:
I am trying to identify bad data. So I want to show postcodes that DO NOT adhere to the standard postcode format. Here is your code. But a postcode like KW1 5LE DOES appear which means the filter is not filtering out the good postcode.
SELECT *
FROM   (SELECT *
         FROM ice1.premise
         UNION ALL
         SELECT *
         FROM ice2.premise) premise    
       where NOT REGEXP_LIKE(postcode, '[A-Z]{2}[0-9] [0-9][A-Z]{2}, i' )

Open in new window

0
VishnukumarCommented:

try this. it is woking for me.

^[A-Z]{2}[0-9] [0-9][A-Z]{2}$
0
SimpleIntelligenceAuthor Commented:
It did not completely solve the problem but sent me on my way.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.