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?

[alan_richmond]

Hi Aar,

looks like spaces in city names are causing issues and the purge is removing characters in city names

Below is the current code and attached is a sample of the output

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

,PurgeChar(PurgeChar(subfield(formatted_address,',',2),'123456789'),Right(trim(Purgechar(subfield(formatted_address,',',2),'123456789')),3)) as City

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

,Keepchar(subfield(formatted_address,',',2),'123456789') as Postcode

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

[sunny_talwar · Accepted solution]

Try this:

Table:

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

  If(Trim(SubField(Address, ',', -1)) = 'USA', Trim(SubField(Address, ',', 2)), Trim(Left(SubField(Address, ',', 2), Index(Trim(SubField(Address, ',', 2)), ' ', -2)))) as City,

  If(Trim(SubField(Address, ',', -1)) = 'USA', Trim(SubField(Trim(SubField(Address, ',', 3)), ' ', 1)), Trim(SubField(SubField(Address, ',', 2), ' ', -2))) as State,

  If(Trim(SubField(Address, ',', -1)) = 'USA', Trim(SubField(Trim(SubField(Address, ',', 3)), ' ', 2)), Trim(SubField(SubField(Address, ',', 2), ' ', -1))) as ZipCode,

  Trim(SubField(Address, ',', -1)) as Country;

LOAD * Inline [

Address

1600 Amphitheatre Pkwy, Mountain View, CA 94043, USA

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

] (delimiter is |);