name parser

R

Roger Converse

I have a couple. What format are the names in?

Basically it would look something like this for:

Last, First MI

SELECT [Conversion Data].[SS#] AS Expr1, [Conversion Data].Name AS Expr2,
InStr(1,[Name],', ') AS LFind, Mid([Name],1,[LFind]-1) AS [Last],
InStr([LFind]+2,[Name],' ') AS FFind,
IIf([FFind]=0,Mid([Name],[LFind]+2),Mid([Name],[LFind]+2,[FFind]-[LFind]-2))
AS [First], IIf([FFind]=0,Null,Mid([Name],[FFind]+1)) AS Middle
FROM [Conversion Data];


If you let me know the format, I can easily tailor the query.

Thanks,
Roger
 
K

Klatuu

Roger's solution will probably be fairly close; however, you will not find
any name parser that can handle every possible name. It is second only to
parsing street addresses in frustration.
Here are some sticky ones:

Hyphenated names:

Rebbeca Crouton-Rimshot

First or Last names with spaces:

Farley Mojo St. James

names with multiple middle names:

James Edwin Autin Healy

confusing ones:

Benjiman Frank Junior

Is the last name Frank or Junior?

And a real life example in an application I did in a different version of
Basic with addresses. To write the code, we used the USPS addressing rules
on how to read addresses.
There are prefixes: West, South, NorthEast, etc.
There are suffixes: same thing
There are street types: Boulevard, Street, Road, etc. with corresponding
abbreviations.

So in testing we found a street address in Houston Texas that is a real
address:

400 West Loop South

It failed because West is a prefix, Loop is a street type, and South is a
suffix.
The street name is actually West Loop and South is actually the suffix, but
when parsing it could not find a street name.
 
R

Roger Converse

Great point, Dave. I probably should have mentioned that there will almost
always be issues because of names like "Mary Beth". In my example below, I
think Beth would end up as the middle name.

Thanks,
Roger
 
K

Klatuu

Roger,
Hope you didn't think I was critizing your work. Not at all. I just wanted
the OP to be aware of the issues.

I just realized my brother's home address would give an address parser fits:

xxxxx Silverwood Bend Lane
 
R

Roger Converse

I did not think that at all. I do not have a ton of experience, so I don't
get to answer many posts, but when I can I try to "give back". Again, I am
glad you brought that up, because surely that will be an issue.

Funny about your brother's address and I am a Junior, so my name doesn't
exactly work either, and I still forgot.

Thanks,
Roger
 
K

Klatuu

Experience doesn't matter if you know the answer.
You will still see me ask a question once in a while when I get into an area
I haven't been in before. For example, I am doing my first ever adp. It is
like a different world. Some code I have been carrying around for years that
is universal stuff doesn't work now.
 
Top