Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a address column where, it is mixed up with street, region, city, pincode. The data is not consistent in hierarchy of the address, sometimes the house number is mentioned and some cases only city and pincode are mentioned.
Now I need to extract only the pincode from this data, below is the type of data i have in the column "Address" and required format of output is in column "Required Pincode". Request you all to help me in sorting this situation.
Address | Required Pincode |
London, W4 1NJ | W4 |
Burney Avenue, Surbiton, EC4A 3HQ | EC4A |
Stanley Hill, Amersham, HP7 9ET | HP7 |
One option is this
TextBetween(Address, ', ', ' ')
TextBetween(', ' & SubField(Address, ', ', -1), ', ', ' ') as [Pincode]
Hi,
try in this way:
Subfield(Trim(Subfield(Trim(Address),',',-1)),chr(32),1)
Hello,
I used the below line of code to reflect the same.
textbetween(SubField(Address,',',-1),' ',' ') as Pincode
One option is this
TextBetween(Address, ', ', ' ')
TextBetween(', ' & SubField(Address, ', ', -1), ', ', ' ') as [Pincode]
Hi,
try in this way:
Subfield(Trim(Subfield(Trim(Address),',',-1)),chr(32),1)
Hello,
I used the below line of code to reflect the same.
textbetween(SubField(Address,',',-1),' ',' ') as Pincode
But i do have one more challenge in extracting the pincode, where there is no pincode at all and there is some location in that, so i need to tag that location's pincode to the address column, added is the description in "Address" column for an airport.
Address | Required Pincode |
London, W4 1NJ | W4 |
Burney Avenue, Surbiton, EC4A 3HQ | EC4A |
Stanley Hill, Amersham, HP7 9ET | HP7 |
Manchester Airport (upon research in internet pincode is M90) | M90 |
But if it is not available, how can we magically create a value... You will need to bring this information from somewhere somehow