Separating a Name Field using the Split Function
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11746
Printed Date: 05 May 2024 at 12:56am
Topic: Separating a Name Field using the Split Function
Posted By: Frank in VA
Subject: Separating a Name Field using the Split Function
Date Posted: 30 Nov 2010 at 3:28am
|
Hi All,
I am trying to separate a name field. The field is coming to me as 'Smith,Joe'. I need to separate the last and first names so I can print them properly. I tried the Split function, but every combination of code I try (examples I found on the web) I get the message:
"The result of a formula cannot be an array."
Can anyone assist me on this?
Thanks! Frank
-------------
Frank
|
Replies:
Posted By: DBlank
Date Posted: 30 Nov 2010 at 3:58am
|
one option...
last name as
left({table.name},instr({table.name},',')-1)
first name as
right({table.name},len({table.name})-instrrev({tabla.name},','))
|
Posted By: JohnT
Date Posted: 30 Nov 2010 at 4:04am
|
Would you list your code showing the Split function ?
|
Posted By: JohnT
Date Posted: 30 Nov 2010 at 5:28am
|
Another option: (this code returns "Last")
StringVar Array x;
x:=split("Last,First",",");
x[1];
|
Posted By: Frank in VA
Date Posted: 30 Nov 2010 at 6:37am
Worked like a charm! As always, thanks again DBlank.
-------------
Frank
|
Posted By: WWTboy
Date Posted: 22 Dec 2010 at 10:52am
|
Thanks. Very helpful. I did run into a problem where in the database, some names do not have a , there is only a space. This errors out. How do I get around a name not having a comma.
How do i separate by space? so it would be an if else with the space check first.
Thanks
|
Posted By: DBlank
Date Posted: 22 Dec 2010 at 11:02am
|
Using John T's formula...
last Name
StringVar Array x;
x:=split({field},(if instr({field},",")=0 then " " else ","));
x[1];
First Name
StringVar Array x;
x:=split({field},(if instr({field},",")=0 then " " else ","));
x[2];
|
Posted By: WWTboy
Date Posted: 06 Jan 2011 at 4:43am
|
Thanks. But I think my suggested solution isn't sound. I now found that we have names with two last name that have no hyphen. So a name like "Williams Adams, Sharon" show up as Adams as last name and Sharon as the first name.
So I guess what I need to do is determine is no comma exists and if there is no comma, then i will put the complete name in the first and last name on the report. Or maybe flag it another way.
Suggestions?
|
Posted By: Keikoku
Date Posted: 06 Jan 2011 at 5:20am
So basically even if a last name has 2 words, it's possible that the person that inputed it into the DB could omitted the comma and assumed that the first name is always the last word?
As a little aside, when we deal with names that have multiple words (ie: first middle1 middle2 last1 last2) we just dump it into "first" and "last" where last is everything else. Separated by a comma.
You will just have to modify the splitting condition a little.
1: if there is a comma, then we know the first name is the 2nd part, and the last name is the first part.
2: if there is no comma, you will potentially end up with three (or more, depending on the name) parts. Suppose my full name is n words long. Then my last name will be the first n-1 words, and my first name is the n'th word. This is assuming my first name does not have multiple words separated by spaces. If it does, and they still leave out the comma, then you are in trouble hehe
Essentially, you can consider running a loop to concatenate the first n-1 words together (with spaces in between), and then set that as the last name. First name will simply be the n'th element.
Or better yet, tell the DBA to stick with one standard and update all the entries so they have commas.
|
Posted By: WWTboy
Date Posted: 06 Jan 2011 at 10:15am
|