Separating a full name field

[tmozer]

The names in my ANALYST table in Oracle are full. First & Last or First, Middle & Last or even Title, First, Middle & Last. A real mixed bag. I would love to separate Last and First for sorting purposes. How would I get started on the logic (formula) to try to do that?? I am using CR 8.5.

 

[bdreed35]

I am sure we can help you out, but you need to show sample data of all the different layout possibilites.

~Brian

 

[tmozer]

Sorry. Here is sample data from the ANALYST.Analyst and ANALYST.Name fields:

MMV123 Marlene M. Valente
TEM Theodore E. Mozer, III
FFL Frederick Loblein
ADD Anthony DeNichilo
JTH Terry Hennessy
TJR Thomas J. Roche
PAV Pedro Velez
TLP Tara Pugnet
KDW Katherine Williams
JT3821 Joseph M. Tafuni

 

[bdreed35]

So the values "MMV123", "TEM", etc are from the Analyst field which probably isn't pertinent for this problem. Tell me if I am wrong.

I think the Name field is what we needed:

Marlene M. Valente
Theodore E. Mozer, III
Frederick Loblein
Anthony DeNichilo
Terry Hennessy
Thomas J. Roche
Pedro Velez
Tara Pugnet
Katherine Williams
Joseph M. Tafuni

To get the first name, I think all you need is this:

split({ANALYST.Name}," ")[1]

The last name portion is a bit trickier:

stringvar strName := replace(replace({ANALYST.Name},",",""),".","");
stringvar array arrName := split(strName," ");

select ubound(arrName)
    case 2 : arrName[2]
    case 3 : if length(arrName[2]) = 1 then arrName[3] else arrName[2]
    case 4 : arrName[3];

~Brian

 

[tmozer]

You are correct (ANALYST.Analyst is not of interest). I will try your code. Thanks.....

 

[tmozer]

We are very close. Two things that screws it up: extra spaces and full middle names. I know these could be corrected in the data, but, it would be nice to code for them....

Amy Marie Rudai
Christopher Piccirillo
Malinda Maslo
Mike Baklarz
Susan Connolly
Teresa J. Melde
Tomas DeLosReyes
Michelle Adamson
Robert Allen Bosley
Stephen M. Andrews
Laura Ann Kushner
Mary Ann Kahoe
Pravin Antala
Stuart Atkinson
Karen Baggitt