Posted by Ed Murphy on 11/28/56 11:57

imani_technology_spam@yahoo.com wrote:

> 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.)

• Next in forum: Re: Query performence about nText
• Prev in forum: Re: Query performence about nText
• Thread view: Re: An interesting Lastname, Firstname, Middlename challenge

[Reply to this message]