| Author |
Message |
Frank in VA
Groupie
Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 46
|
   Topic: Separating a Name Field using the Split Function
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
Edited by Frank in VA - 30 Nov 2010 at 3:28am
|
|
Frank
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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},','))
|
|
IP Logged |
|
JohnT
Groupie
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
|
Posted: 30 Nov 2010 at 4:04am |
|
Would you list your code showing the Split function ?
|
|
IP Logged |
|
JohnT
Groupie
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
|
Posted: 30 Nov 2010 at 5:28am |
Another option: (this code returns "Last")
StringVar Array x;
x:=split("Last,First",",");
x[1];
|
|
IP Logged |
|
Frank in VA
Groupie
Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 46
|
Posted: 30 Nov 2010 at 6:37am |
|
Worked like a charm! As always, thanks again DBlank.
|
|
Frank
|
|
IP Logged |
|
WWTboy
Newbie
Joined: 22 Dec 2010
Online Status: Offline
Posts: 5
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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];
Edited by DBlank - 22 Dec 2010 at 11:04am
|
|
IP Logged |
|
WWTboy
Newbie
Joined: 22 Dec 2010
Online Status: Offline
Posts: 5
|
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?
|
|
IP Logged |
|
Keikoku
Senior Member
Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
|
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.
Edited by Keikoku - 06 Jan 2011 at 5:26am
|
|
IP Logged |
|
WWTboy
Newbie
Joined: 22 Dec 2010
Online Status: Offline
Posts: 5
|
Posted: 06 Jan 2011 at 10:15am |
|
|
|
IP Logged |
|
|
|
