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?
 
Paul_Harris_FusionConnect With a Mentor Commented:
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
VishnukumarConnect With a Mentor Commented:

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
All Courses

From novice to tech pro — start learning today.