How t separate postcode from complete address field?

[#1]

Hi,

I have address field as below in table

4 Mountview, Keady, wards HJ60 3RG

34 Wyndell Heights, wards HJ23 7GX

234 Bawnmore Park, wards HJ36 7BQ

56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ

Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ

90 Leven Park, Huyyijane, HJ5 7JA

98 Dunsuivnish Avenue, wards HJ55 7EP

5e Castle Hill, Toristreet, HJ70 1JP

23 Dunsuivnish Avenue, Toristreet, HJ55 7EP

78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB

90 Abbey Ring, Holywood, HJ18 9NS

23 Abbey Ring, Holywood, HJ18 9NS

I need to separate Postcdoe from complete address. Always postcode starts with 'HJ'. how to separate postcode from entire address for each address value.

Please can anyone suggest me how to do this.

Thanks.

[sunny_talwar]

I thought of doing it this way, but the only problem is that if there is another HJ in the SubField('...', 'HJ', 2) might not give you the right result.

[sasiparupudi1]

Hi Sunny

The address looks like in mixed case.. so it might not be unsafe to use HJ as a delimeter

Sasi

[#4]

Thanks. working.

[#5]

Thanks for your help.

[#6]

Thanks. working.

[d_koti24]

Hi,

you can try like this also,This is the simplest way.

address:

LOAD * INLINE [

address

"4 Mountview, Keady, wards HJ60 3RG",

"34 Wyndell Heights, wards HJ23 7GX",

"234 Bawnmore Park, wards HJ36 7BQ",

"56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ",

"Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ",

"90 Leven Park, Huyyijane, HJ5 7JA",

"98 Dunsuivnish Avenue, wards HJ55 7EP",

"5e Castle Hill, Toristreet, HJ70 1JP",

"23 Dunsuivnish Avenue, Toristreet, HJ55 7EP",

"78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL",

"12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL",

"2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ",

"11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ",

"67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE",

"43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE",

"45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB",

"90 Abbey Ring, Holywood, HJ18 9NS",

"23 Abbey Ring, Holywood, HJ18 9NS"

];

ad:

load

mid(mid(address,index(address,',',-1)+1,len(address)),index( mid(address,index(address,',',-1)+1,len(address)),'H')) as Pincode

Resident

address;

drop table address;

Regards,

Koti

[d_koti24]

Hi Sunny,

can i write Like this?

load

mid(mid(address,index(address,',',-1)+1,len(address)),index( mid(address,index(address,',',-1)+1,len(address)),'H')) as Pincode

Resident

address;

[sunny_talwar]

It seems to be doing the job . But seems a little complicated to comprehend, although what I proposed might not be easy as well. I guess it comes down to testing it extensively with the actual data to see which one is better all the time. If both are correct, the developer can choose whichever is easier for them to comprehend.

Best,

Sunny