Use string manipulation
index(string1, ' ')
this will give the position of the blank space.
Assuming the blank space is the delimiter here between teh firstname and lastname
next use left(string1,<result we got from index>)
should give the last name
right(string1,<result we got from index>+1, len(string1))
should give the first name.
We are not depending on capitalization here.
LOAD * INLINE [
THE MISTER Someone Something
LOAD recno() as recID,if(ord(right(NamePart,1))<97,'UPPER','LOWER') as Case, *;
LOAD NAME, subfield(NAME,' ') as NamePart resident NAMES;
left join (NAMES) LOAD NAME, concat(Capitalize(NamePart),' ', recID) as Firstname Resident TMP where Case ='LOWER' group by NAME;
left join (NAMES) LOAD NAME, concat(Capitalize(NamePart),' ', recID) as LastName Resident TMP where Case ='UPPER' group by NAME;
drop table TMP;
I Capitalized the Last Name to The Mister, but you can just remove the Capitalize() function, if you want to get all in UPPER case THE MISTER.