Separating a Varchar name field into fixed fields in Te

[jamcats]

I am trying to separate a varchar (40) name field into 3 fields.

Last Name
First name
Middle name

The only thing that separates the name on the input file is spaces, none of the fields are in a fixed position.

Example of Input:
Mary Ann Jones
Joseph D Anderson
Lawrence Ambrose Olson



This is how I want them to be:
Mary Ann Jones
Joseph D Anderson
Lawrence Ambrose Olson

Any suggestions how to do this in teradata/Bteq ?


Kathy Nordstrom
Lost in CyberSPace

 

[dnoeth]

You can do that, but it will result in code with nested POSITION/SUBSTRING, which is almost unreadable und quite hard to maintain.
And it will break, if there's a missing blank or a comma instead of blanks. So you'll start to add conditions for those special cases and then it's even harder to read...

Try to do it during the load using an INMOD.

Dieter