PDA

View Full Version : breakup fields


ABC123

lkatz1
02-14-2005, 03:43 PM
How can we break up the fields like mr john doe but some have 2 first names but we want to do a word cound from the mrs for ex; mr & mrs will start counting if there is 3 words after that means there is 2 first and middle name into a new field of (first name) and the last word into field (last name)
also is there anyway to eliminate if there is no first name ant the field first name is going to equal to same wording from last name it should blank out first name

Tom Cone Jr
02-14-2005, 04:53 PM
I'd break the main table up into smaller tables each of which have the same number of words in the composite name field use an update operation to populate title or salutation first middle last and suffix fields append the smaller tables back into a single table when all processing was concluded -- tom

lkatz1
02-14-2005, 05:23 PM
how would you break it up with automatic without having to go each record indvidually

Tom Cone Jr
02-14-2005, 10:02 PM
Alpha Five has a whole host of character based functions you will need to use a lot of them so its time to get the help file out and begin studying here are some examples I created for you using the interactive editor maybe theyll give you some ideas the basic strategy Id follow would be to run a query to narrow the records to those which have something in common might be those where the word count is the same might be those where the first 11 chars are Mr. & Mrs. then Id use a series of update operations to parse the fullname field into its pieces assigning each to the desired field after all the records in the current filtered subset have been processed id run another query against the whole table to extract the next subset of records with similarly structured fullname field values examples follow

dim fullname as C
fullname = "Mr. & Mrs. John Paul Jones"
?w_count(fullname)
= 6
?left(fullname,11)
= "Mr. & Mrs. "
?right(fullname,len(fullname)-11)
= "John Paul Jones"
?lastword(fullname)
= "Jones"
?word(fullname,1)
= "Mr"
?word(fullname,2)
= "&"
?word(fullname,3)
= "Mrs"
?word(fullname,4)
= "John"
?word(fullname,5)
= "Paul"


fullname = "Mrs. June Carter-Cash"
?w_count(fullname)
= 3
?left(fullname,5)
= "Mrs. "
?right(fullname,len(fullname)-5)
= "June Carter-Cash"
?lastword(fullname)
= "Carter-Cash"
?word(fullname,1)
= "Mrs"
?word(fullname,2)
= "June"
?word(fullname,3)
= "Carter-Cash"

-- tom

Tom Cone Jr
02-14-2005, 10:23 PM
I should have also mentioned that there is a genie available for update operations one of the things it can help you with is breaking a name into its parts you should check it out -- tom

Raymond Lyons
02-15-2005, 05:13 PM
David,

Below is an old posting from the v4 forum. It is probably still relevant to v5 and v6.

Hope it helps.

Ray
***********************************
The Genie does work on simple cases, but none of my cases are simple. I always seem to end up with a name field something like "Mr. Peter Du Charme Sr" and for these the genie is of little help.

But, you can write your own updates to strip out and rearrange various things. The sample below should give you some idea of how to proceed, though as usual someone on this board will have a "far better way" to do what I do. First, I always work on a copy of my original data so if I screw it all up I can go back to the original. In my case, the name in the single name field is frequently (but not always) is preceded by Mr or Ms, and where appropriate a suffix of Sr, Jr, III, etc. As I recall, the names may also have a middle initial but when they do I don't care about the initial, so if I remember correctly I strip it out. Thus in my case I might have MR PETER K WAYNE SR and all I want to end up with is First = PETER and Last = WAYNE. So I start with a Name field that has MR PETER K WAYNE SR and I end up with:
Name: PETER WAYNE
First: PETER
Last: WAYNE

But when Name starts out MR FRANK DU CHARME JR, I end up with
Name FRANK DU CHARME
First: FRANK
Last: DU CHARME

As I said, I could put the middle initial in a separate field, but my application doesn't care about middle initials.

In any case, perusing the following update expressions should give you ideas for how to proceed with your own needs. All these updates are run at the same time by the way. You just have to make sure they are run in the order listed. You could also strip out suffixes like MD if you data had them (mine don't). Oh yeah, it has been a while since I looked at or ran the updates, so it's always possible there is a problem with them, though I don't think so or I would not be posting them.

Raymond Lyons

********************

Name: IF(UT(WORD(NAME,1))="MR".OR. UT(WORD(NAME,1))="MS",WORD(NAME,2)+" "+WORD(NAME,3)+" "+WORD(NAME,4)+" "+WORD(NAME,5)+" "+WORD(NAME,6),NAME)

Name: IF(WORD(NAME,-1)="JR".OR.WORD(NAME,-1)="SR".OR.WORD(NAME,-1)="II".OR.WORD(NAME,-1)="IV",LEFT(NAME,LEN(TRIM(NAME))-2),IF(WORD(NAME,-1)="III",LEFT(NAME,LEN(TRIM(NAME))-3),NAME))

Now the first name is simple!

First: WORD(NAME,1)

It's the Last name that gets complicated (and would have to be adjusted for whatever name combinations are common in your area). And of course there will always be a few that need to be manually adjusted.

Last: IIF(W_COUNT(NAME)=2,WORD(NAME,2),IF(LEN(WORD(NAME,2))=1,ALLTRIM(SUBSTR(NAME,(LEN(WORD(NAME,1))+3),LEN(ALLTRIM(NAME)))),IF(LEN(WORD(NAME,2))=2.OR.WORD(NAME,2)="MAC".OR.WORD(NAME,2)="VAN".OR.WORD(NAME,2)="DEL".OR.WORD(NAME,2)="SAN".OR.WORD(NAME,2)="VON",ALLTRIM(SUBSTR(NAME,(LEN(WORD(NAME,1))+1),LEN(ALLTRIM(NAME)))),WORD(NAME,-1))))