Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I got a field with different data in it. For an example, the string got the job classification as the first word followed by the Name and sometimes (Grad) or (Post Grad) at the end. refer the below data set.
'ANUM COLLIN, Berry'
'NUM MARGE, Jodie'
'NUM LEE, Mary (Post Grad)'
'RN TASKER, Peter (Grad)'
'RN McDONALD, Ben (Grad)'
'NUM MORFITT, Sunny'
'RN VAN VARUN, Deb (Grad)'
I want to retrieve only the full name of the employee removing the first word and anything after "(" character.
I used "Trim(SubField(Name,'(',1)) As NewName" to remove anything after the "(" character and could get the first word removed from the string.
Can anyone help me with this?
Also, I found, even after using the Trim function, still I get some trailing spaces at the end of the NewName field. Would appreciate your comments on this too.
Thank you in advance.
Kind regards,
Andrew
May be this
Table: LOAD *, Trim(Mid(SubField(Name & '(', '(', 1), Index(Name, ' ') + 1)) As NewName; LOAD * INLINE [ Name "ANUM COLLIN, Berry" "NUM MARGE, Jodie" "NUM LEE, Mary (Post Grad)" "RN TASKER, Peter (Grad)" "RN McDONALD, Ben (Grad)" "NUM MORFITT, Sunny" "RN VAN VARUN, Deb (Grad)" ];
May be this
Table: LOAD *, Trim(Mid(SubField(Name & '(', '(', 1), Index(Name, ' ') + 1)) As NewName; LOAD * INLINE [ Name "ANUM COLLIN, Berry" "NUM MARGE, Jodie" "NUM LEE, Mary (Post Grad)" "RN TASKER, Peter (Grad)" "RN McDONALD, Ben (Grad)" "NUM MORFITT, Sunny" "RN VAN VARUN, Deb (Grad)" ];
Hi,
Try below:
DATA: LOAD Name, MID(SUBFIELD(Name,',',1),INDEX(Name,' ')+1)&', '&SUBFIELD(trim(SUBFIELD(Name,',',2)),' ',1) AS NewName ; LOAD * INLINE [ Name "ANUM COLLIN, Berry" "NUM MARGE, Jodie" "NUM LEE, Mary (Post Grad)" "RN TASKER, Peter (Grad)" "RN McDONALD, Ben (Grad)" "NUM MORFITT, Sunny" "RN VAN VARUN, Deb (Grad)" ];
Hi Sunny,
Thank you very much.
Does the job perfectly.
Kind regards,
Andy
Hi Crusader,
Thank you very much for your prompt reply.
When there are additional spaces before the first name (after the ","), the expression does not give the expected outcome.
However, Sunny's expression worked.
Nevertheless, thank you very much for your excellent feedback.
Kind regards,
Andy