topic Re: Extract UK PostCode from a string in Talend Studio

Extract UK PostCode from a string

raheemrao — Tue, 11 Oct 2016 11:35:42 GMT

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

Re: Extract UK PostCode from a string

Anonymous — Tue, 11 Oct 2016 14:32:29 GMT

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

Re: Extract UK PostCode from a string

raheemrao — Tue, 11 Oct 2016 14:36:02 GMT

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

Re: Extract UK PostCode from a string

Anonymous — Wed, 12 Oct 2016 06:04:56 GMT

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-string

You can follow this URL for more details.

Re: Extract UK PostCode from a string

raheemrao — Wed, 12 Oct 2016 09:05:09 GMT

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;

Re: Extract UK PostCode from a string

Anonymous — Wed, 12 Oct 2016 10:25:29 GMT

Hello

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

Regards
Shong

Re: Extract UK PostCode from a string

raheemrao — Wed, 12 Oct 2016 14:04:42 GMT

Thaks Shong, i try tExtractRegexFields

Re: Extract UK PostCode from a string

Anonymous — Mon, 13 Jan 2020 10:13:06 GMT

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?