Extracting states from an address field

[#1]

Hi All

I have an address field and in order to categorise the states into a region, I need to extract the specific 3 digit state code.

addresses are pretty standard i.e. P. Sherman 42 Wallaby Way, Sydney, NSW, 2000 and I am looking to get the state code.

I'm and Aussie so we only have the following state and territory codes required.

ACT, NSW, QLD, TAS, WA, SA, NT, VIC

The problem is that none of the addresses have a standard delimiter, nor position within the data for the state code and some of them are missing altogether (I would use an NA result for those if possible)

Can you please have a think and let me know if this is possible? I am investigating substring functions and will post an answer if I can come up with one!

Oh and this needs to be in the script so I can use an applymap function to categorise them

[#2 · Accepted solution]

StateMap:

Mapping

LOAD * INLINE [

    StateCode, State

   0,NA

    1, ACT

    2, NSW

    3, QLD

    4, TAS

    5, WA

    6, SA

    7, NT

    8, VIC

];

Load *, ApplyMap('StateMap',Statecode) as State;

Load Address,WildMatch(Address,'*ACT*', '*NSW*', '*QLD*', '*TAS*', '*WA*', '*SA*', '*NT*', '*VIC*') as Statecode;

LOAD * INLINE [

    Address

    "P. Sherman 42 Wallaby Way, Sydney, NSW, 2000"

    "P. Sherman 42 Wallaby Way, Sydney, QLD, 2000"

    "P. Sherman 42 Wallaby Way, Sydney TAS 2000"

    "WA 3000"

    "XXXX YYYYY"

];

[Anonymous]

Hi,

Use the mid  function.

Example:

I have the date like 01-Feb-2014

now my requirement is to get only Feb as output for that I'll written the condition like

Mid(Date,3,3) -> O/p is Feb

[#4]

Thankyou Uday!

I will apply this to my model

Greatly appreciated!!