Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
raheemrao
Contributor
Contributor
‎2016-10-11 07:35 AM

Extract UK PostCode from a string

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

Labels (2)
Labels
1,178 Views
0 Likes
Anonymous
Not applicable
‎2016-10-11 10:32 AM

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
1,178 Views
0 Likes
raheemrao
Contributor
Contributor
‎2016-10-11 10:36 AM

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
1,178 Views
0 Likes
Anonymous
Not applicable
‎2016-10-12 02:04 AM

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.
1,178 Views
0 Likes
raheemrao
Contributor
Contributor
‎2016-10-12 05:05 AM

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;
1,178 Views
0 Likes
Anonymous
Not applicable
‎2016-10-12 06:25 AM

Hello

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

Regards
Shong
1,178 Views
0 Likes
raheemrao
Contributor
Contributor
‎2016-10-12 10:04 AM

Thaks Shong, i try  tExtractRegexFields
1,178 Views
0 Likes
Anonymous
Not applicable
‎2020-01-13 05:13 AM

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?
1,178 Views
0 Likes