Use REGEXP to help clean up POSTCODE field

Cheers Paul. Will try it after a coffee.

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?

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}

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

// can you show me the expression that would identify

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

 

I want the postcode KW1 5LE to be picked up by the REGEXP function.

[A-Z]{2}[0-9]  [0-9][A-Z]{2}

Its still not picking up

KW1 5LE

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:]]...  $'

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' )

Select allOpen in new window

It did not completely solve the problem but sent me on my way.