split up an address into parts

[alan_richmond]

Hi I am looking for the best way in a load script to split up an address to fields of its parts.

as an example a single address field of ..  full_address of 1600 Amphitheatre Pkwy, Mountain View, CA 94043, USA

to be split up to

Country of USA

Zipcode of 94043

State of CA

City of Mountain View

Address of 1600 Amphitheatre Pkwy

Is subfield the best way or right or left or mid?

[sunny_talwar]

May be SubField with comma as delimiters

SubField(Address, ',', 1) as Address,

SubField(Address, ',', 2) as City,

SubField(SubField(Address, ',' , 3), ' ', 1) as State,

SubField(SubField(Address, ',' , 3), ' ', 2) as ZipCode,

SubField(Address, ',', 4) as Country

The above heavy rely on the data being consistent.

[aarkay29]

Try this

subfield(full_address,',',1) as Address

subfield(full_address,',',2) as City

subfield(full_address,',',4) as Country

Purgechar(subfield(full_address,',',3),'0123456789') as State

keepchar(subfield(full_address,',',3),'0123456789') as ZIP

[alan_richmond]

Hmm,

I also have Australian address like ..

1-2 Main St, Box Hill VIC 3128, Australia

where there is not a comma after the City

[sunny_talwar]

I guess if you can provide 10-15 lines of different variation of address, we might be able to provide you with a sample which might work across all the different addresses

[alan_richmond]

Both are the output of the googleapis geocoder so it looks like all the Australian addresses miss the comma after city and the US addresses use the comma.

the Australian zipcode is 3 or 4 numbers and the state is always 2 or three characters and there are only 8 possible state names

[aarkay29]

How about this, Only works for Australia use it in a if statement

subfield(full_address,',',1) as Address,

PurgeChar(Purgechar(subfield(full_address,',',2),'123456789'),Right(Trim(Purgechar(subfield(full_address,',',2),'123456789')),3)) as City,

Right(Trim(Purgechar(subfield(full_address,',',2),'123456789')),3) as State,

Keepchar(subfield(full_address,',',2),'123456789') as Zip,

[alan_richmond]

worked like a charm, thanks Aar

[alan_richmond]

Oops, missed Country

[aarkay29]

subfield(full_address,',',3) as Country

only for Australia though

subfield(full_address,',',4) as Country for US