Extract UK PostCode from a string

[raheemrao]

Hi,
      I have a table which contains UK full addresses, I wanted to get postcode from this column and insert into new postcode column.
i wanted to extract post code from attached example, as you can see in some cases address only contain post code but in some cases it's full address, sometime post code have space in middle and on some records without spaces, but post code will not be more than 7 character.
CLACTON                CO154XA
CLAYE SOUILLY          774 10
CLYDEBANK              G81 2RR
CM14 4BY
CM1 1XR
COPENHAGEN
County Antrim     BT37 9AQ

thanks

[Anonymous]

Hi
Can you show me what are the postcode of each row in this given example? For example, I want to know the postcode is  774 10 or 10 in the second row?

Regards
Shong

[raheemrao]

Hi,
    from the example please see post codes
CLACTON                CO154XA
CLAYE SOUILLY          774 10
CLYDEBANK              G81 2RR
CM14 4BY
CM1 1XR
COPENHAGEN
County Antrim     BT37 9AQ
------------------------POSTCODES--------------------
CO154XA
77410
G81 2RR
CM14 4BY
CM1 1XR
COPENHAGEN  --(THERE IS NO POSTCODE IN THIS ROW)
BT37 9AQ

[Anonymous]

I guess using a Rgex to identify the Postal code might work for you. 

http://stackoverflow.com/questions/8655269/regex-to-extract-part-of-a-uk-postcode-from-an-address-st...

You can follow this URL for more details.

[raheemrao]

thanks for reply  sankalp, i have tried regex expression in oracle sql and it works but i don't know how to use this talend tmap.
this is works for me in sqlplus
SELECT  REGEXP_SUBSTR('ALTRINCHAM             WA141RF', '[A-Z]{1,2}[0-9]{1,2} ?[0-9][A-Z]{2}')
from dual;

[Anonymous]

Hello

I think tExtractRegexFields can fit your request, this component is used to extract data from a column with regex expression.

Regards
Shong

[raheemrao]

Thaks Shong, i try  tExtractRegexFields

[Anonymous]

hello everyone here. you all talk about addresses, distance in kilometers ... but how do you know? and how do they know it? is there a postcode needed? is it really that serious, but what if I find out the postcode https://postcodefinder.net, but it turns out to be too far, what will happen? sure I want to be closer because we want to get into Kiasu, but is it just about the distance?