Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
From the right, use -1
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.
Thanks Bruno, that worked!
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!!!