field trimming

[needmoremoney]

Hello
I have a name field that needs to be trimmed.

Name field has Firstname such as:

John Jr.
Cindy S
Ivannofth IV
..

I want to trim off the middle name and just have the first name only. I also need to place the Jr., S and IV as a middle name for a second field. Any help thanks.

 

[needmoremoney]

I'm sorry, I forgot to mention that I am using Crystal Reports 8.5.

 

[Madawc]

Try Split (Firstname). This will return an array of two elements for the data you showed, with the middle name as the second element.


Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[lbass]

For the first name field, you would use:

split({table.firstname}," ")[1]

For the middle name, try a formula like:

if ubound(split({table.firstname}," ")) > 1 then
split({table.firstname}," ")[2]

-LB

 

[synapsevampire]

Parsing names is generally fairly complex, and your programmers and dba should be sent out for a seminar on collecting data, and while they're out, have their badges deactivated.

Why you would have Jr as part of the First name is very odd, as it's usually listed after the Last name.

As examples, names can be:

John F. Kennedy Jr.
Oscar De La Hoya

So you probably need to code for the possibilities, and design better tables to include:

Prefix
First
Middle
Last
Suffix

and perform some training for data collection.

You might elaborate on LB's suggestion and get pretty close, as in:

whileprintingrecords;
stringvar first:="";
stringvar middle:="";
stringvar prefix:="";
stringvar suffix:="";
first := split({table.name}," ")[1];
if uppercase(split({table.name}," ")[ubound(split({table.name}," "))]) in ("JR","I", "II", "III", "IV", "V", "SR") then
suffix := split({table.name}," ")[ubound(split({table.name}," "))];

You may need to elaborate on the list of possible suffixes, but this will store the data into the proper variables of first and suffix, which you can later reference, or do so here.

Again, it's difficult to resolve bad data wihout lots of coding. I used to manage some hefty name and address lists and had created 1000's of lines of proprietary code to clean up names based on common names lists.

-k

 

[needmoremoney]

Kool, I'll try those out.

By the way, the data are from a client. Even the name is specifically labeled "Firstname" some people choose to input in what ever they want. By the time I get the request, there's been months of data already.

I found another method, I have the a search feature to look for a " " and trim the rest off. I'll post it when I get it working.

Thanks much.