Extracting Alphanumeric content from a string

[QlikView_99]

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

[sunny_talwar · Accepted solution]

One option is this

TextBetween(Address, ', ', ' ')

TextBetween(', ' & SubField(Address, ', ', -1), ', ', ' ') as [Pincode]

[StarinieriG · Accepted solution]

Hi,

try in this way:

Subfield(Trim(Subfield(Trim(Address),',',-1)),chr(32),1)

[QlikView_99 · Accepted solution]

Hello,

I used the below line of code to reflect the same.

textbetween(SubField(Address,',',-1),' ',' ') as Pincode

[QlikView_99]

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

[sunny_talwar]

But if it is not available, how can we magically create a value... You will need to bring this information from somewhere somehow