An interesting Lastname, Firstname, Middlename challenge

I'm sure you all have seen situations where a field has a combined name
in the format of "Lastname, Firstname Middlename" and I've seen
examples of parsing that type of field. But what happens when the data
within this type of field is inconsistent? Here are some examples

Apple, John A.
Berry John B.
CherryJohn C
Donald John D

How does one parse the data when the data isn't consistent?imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

I'm sure you all have seen situations where a field has a combined name
in the format of "Lastname, Firstname Middlename" and I've seen
examples of parsing that type of field. But what happens when the data
within this type of field is inconsistent? Here are some examples
>
Apple, John A.
Berry John B.
CherryJohn C
Donald John D
>
>
How does one parse the data when the data isn't consistent?

Conditionally. Something along the lines of (I'll use a mixture of
SQL and pseudocode)

select
case when name like '<last>, <first<m>.' then <last>
when name like '<last>, <first<m>' then <last>
when name like '<last<first<m>.' then <last>
when name like '<last<first<m>' then <last>
else name last_name,
case when name like '<last>, <first<m>.' then <first>
when name like '<last>, <first<m>' then <first>
when name like '<last<first<m>.' then <first>
when name like '<last<first<m>' then <first>
else '' first_name,
case when name like '<last>, <first<m>.' then <m>
when name like '<last>, <first<m>' then <m>
when name like '<last<first<m>.' then <m>
when name like '<last<first<m>' then <m>
else '' middle_initial
from (etc.)|||>How does one parse the data when the data isn't consistent? <<

I get a mailing package and save myself a lot of problems -- why
re-invent the wheel?

Group 1 Software , Melissa Data Corporation and SAA are such
companies. They can scrub mailing lists MUCH better than you can --
unless you want to make a major project of it for a few years.