Please choose more meaningful subjects for your posts, one that is actually related to the question being asked.
As to your issue, spell out in words - regardless of the tool used - the process of parsing the name and rules for determining if a middle name is present or not. Once you do, how you would attempt to do this in DataStage should be much clearer.
-craig
"You can never have too many knives" -- Logan Nine Fingers
This is a delimiter problem, and the root cause is the rules (or lack of them) at the data source.
If the main record delimiter is a comma, parse into last-name and first-middle. If there will always be a single space between the first name and middle name (full or initial), reparse first-middle into first-name and middle-name with space as the delimiter.
You main problem is that you cannot code for humans who are allowed to change their minds about how they enter data. With valid variations in names like compound names with a space in between, you see the problem. It can be solved only by imposing requirements at the data entry point and refusing to accept exceptions.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
... or using something like QualityStage to figure it out based on known first names. But even that's not 100%. Can't be, because parents go on inventing new names for their children.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.