Parse name field into components

[JenJohnson]

Good Day, All:

I have a database field called Manager_Name which contains data, some of which looks like this:

C. Lennis Koontz,II
Theresa Holt
Victor T. Murhman
Paul A. Graham, Jr.
Thomas Murphy Jr.
Jon B. Lovelace Jr.
David B. Rees III
Mary Jo Ochson
M. L. Conery
Michael A. Del Balso

I need to parse out the last name of each of these records. (The last name I'm expecting is in red).
Has anyone else had the need to do something like this?

Any help would be greatly appreciated.

Thanks,
Jennifer

 

[Massy]

Hi Jennifer,

You could do something like this:

select right( Manager_Name, char_length( Manager_Name) - locate( Manager_Name, ' ', -1)), Manager_Name from table;

Of course, this fails when the manger_name contains inconsistencies such as 'Jr', 'III' or 'Del Balso'. You could get round this by forming a union and hardcoding the results (or hardcoding the results in another table).

However, a far superior solution is to split the column into two separate columns such as manager_lname and manager_fname.

Tom.

 

[JenJohnson]

Hi Tom,

Thanks for the suggestion. I had problem with "locate" function, however. Got message that function does not exist.

I agree that a better solution is to split out the name, but we get this file from an outside vendor, and getting this particular vendor to do anything "custom" very difficult.

Thanks again,
Jennifer

 

[Massy]

Opps, it seems the "locate" function is an ASA SQL vendor extension. Must take more notice of "standards"!!!