Need help splitting a field using the first delimiter from the right.

[#1]

I've been using the script below to split a field into two:

If(Index(AccountTemp,' ')=0,Null(),SubField(AccountTemp,' ',1))                      AS Account,

If(Index(AccountTemp,' ')=0,AccountTemp,SubField(AccountTemp,' ',2))          AS AccountGroup,

which works for:

Denver Admin ---> Denver,      Admin,

But doesn't work for:

Washington DC Admin ------->               Washington,                DC,               Admin,

Is there a way to split a field using the first space in the data from the right?

[Anonymous]

From the right, use -1

[Anonymous · Accepted solution]

Hello, Trey.

Try this:

Left(AccountTemp, Index(AccountTemp, ' ', -1) - 1)    as Account,

Mid(AccountTemp, Index(AccountTemp, ' ', -1) + 1)   as AccountGroup,

Index() with a -1 third parameter finds the first occurrence of the substring from the right.

Regards.

[#4]

Thanks Bruno, that worked!

[#5]

HI Trey,

Kindly Try Like this ..

Test:

LOAD * INLINE [

   AccountTemp

    Denver Admin

    Washington DC Admin

];

load

left( AccountTemp,index(AccountTemp,' ',-1))as Account,

Right(AccountTemp,len(AccountTemp)-index(AccountTemp,' ',-1))as AccountGroup

Resident Test;

Drop Table Test;

Thanks!!!