Hi All,

I need to build an output to take a single name field and separate it into LASTNAME, FIRSTNAME, and MIDNAME.

I've played with @instr(FULLNAME," ") to try to break it down, but my problem is this -- the name could have two, three or four elements.

John Smith
J R Smith
John R Smith
John R S Smith

Has anyone done this work sort of work, and figured out a good routine for separation?

Thanks!

I tried something similar years ago.  It's a lot more complicated than that.  My sister would tell you adamantly that her first name is Mary Ann, not Mary.

In practice there are loads of problem names:

Mary Ellen | Johnson
Mary Jane |
Bobbie Sue |
Betty Sue |
Betty Jean |
John-Paul | Belmondo
Billy Joe | Wilde
Bobby Joe | Carter
Maria | de la Garza
Juan | Gomez y Sanchez (or maybe Gomez-Sanchez)
Harold | Jackson IV
Joseph Thomas Richard Reinckens II (my full name)
Harold | von Steiner
Jean-Claude | Camille | François | Van Varenberg
(a/k/a) Jean-Claude | van Damme
Hillary | Rodham | Clinton -- what is "Rodham"?

Like "de la Garza" in Spanish, there are many similar multi-word Italian surnames common in the U.S.

And those are just some quick examples I remember off the top of my head!  They don't even include the fact that with Korean names the surname comes first, e.g., Kim Jong Il.

They also don't include where the person includes a nickname: John "Jack" Kennedy or John (Jack) Kennedy

One other thing regarding "Hillary Rodham Clinton".  In her case it is maiden_name + married_name. But what you see a lot now in the younger non-Hispanic generation is the mother's surname being used as a middle name, e.g., Thomas Rodham Clinton where the person would consider Rodham to be a middle name.  What about Ann Rodham Jackson? Is Rodham her "middle" name or is she using maiden + married?  Would she sign her name Ms. Rodham Jackson or Ms. Jackson?

(And just for the heck of it: Harry S Truman. No, there's not supposed to be a period after the S.  It's not an initial! His middle "name" was just the letter S!)

Here's what I've come up with to test my database ... seems to be working ... I'm currently dicarding more than one middle name, and any prefixes or suffixes that are not directly name-related on purpose.

var vFullName as string
var vNameTitle as string = "Dr;Dr.;Doctor;Mr;Mr.;Mister;Mrs;Mrs.;Missus;Ms;Ms.;Miss;Jr;Jr.;Sr;Sr.;I;II;III;IV;V;VI;VII;VIII;DDSMD;P.Eng"
var vCleanName as string
var vFirstName as string
var vMiddleName as string
var vLastName as string

var vNameCount as int
var vCleanNameCount as int


vFullName = DBName
vFullName = @Replace(vFullName," ",";")
vNameCount = @countstringarray(vFullName)

vCleanName = @DifferenceStringArray(vFullName,vNameTitle)
vCleanNameCount = @countstringarray(vCleanName)

if vCleanNameCount = 2 then
{
	vFirstName = @accessstringarray(vCleanName,1)
	vLastName = @accessstringarray(vCleanName,2)
}

if vCleanNameCount = 3 then
{
	vFirstName = @accessstringarray(vCleanName,1)
	vMiddleName = @accessstringarray(vCleanName,2)
	vLastName = @accessstringarray(vCleanName,3)
}

if vCleanNameCount >3 then
{
	vFirstName = @accessstringarray(vCleanName,1)
	vMiddleName = @accessstringarray(vCleanName,2)
	vLastName = @accessstringarray(vCleanName,vCleanNameCount)
}

WriteLn (vFirstName)
WriteLn (vMiddleName)
WriteLn (vLastName)