Splitting one field into two via query 1

[Peterout]

I am new to Foxpro(v5). I have been using Access for about 5 years. In Access I use an update query to split a 'Name' field into two other fields by looking for a comma or space between words in the 'Name' field. For example I will have Smith, John in the 'Name' field and I split the words into two separate blank fields (LastName, FirstName) buy having the Update query looking for the comma seperation.

I am using Foxpro now since I have very large dbf files to update. I ran the query in Foxpro but cannot find the right expression to split one field into two others. It seems the expressions for Foxpro and Access are quite different.

Can anyone help me with the proper expression for Foxpro?

Thanks

Peter

 

[JDurbin]

The AT() function is commonly used for finding a character in a string. I'm not sure what you are doing in your query and update. John Durbin
john@johndurbin.com
MCP Visual FoxPro
ICQ VFP ActiveList #73897253

 

[Peterout]

Thanks John:

But I'm still lost. I use foxpro only for working on dbf files containing names and addresses. I do high volumed data processing for address verification and sorting. I get some customer files that have a field containing both the lastname and firstname. When I was using Access I created 2 other fields called 2 and 3. I would rename the customer's name field to 1. The I would do an Update query with the following:
Left$([1],InStr(1,[1],[delimiter])) :This is for updating field 2

Right$([1],Len([1])-InStr(1,[1],[delimiter])) :This is for updating field 3

Now I find that I need Foxpro since I am working with address databases larger than 600,000 records. However I find that the codes I used in Access are quite different than foxpro.

Any help would be much appreciated.

Thanks

Peter

 

[Diederik]

Well, the differences are not that big.
Right$ en Left$ are equivalent to the Fox RIGHT() and LEFT() functions.
Next to that, you can also use SUBSTR().
And we need John's AT() function...

Let's say you have a FULLNAME and you want to split it up to a FIRSTNAME and LASTNAME, and the separator is a single space:

?fullname          && "John Doe"
nSplitPosition = AT( space(1), fullname )
firstname = LEFT(fullname, nSplitPostition - 1)
?firstname            && "John"
lastname  = SUBSTR(fullname, nSplitPosition + 1)
?lastname             && "Doe"
lastname = RIGHT(fullname, LEN(fullname) - nSplitPostion)
?lastname             && "Doe"

Diederik Vermeeren
verm1864@exact.nl

 

[Peterout]

Thanks Diederik,

I have tried your suggestion in conjunction with the UPDATE function.

When I apply the update the first record in the Firstname field is split properly. However all the following records are not split by the space but rather by the same number of charcters that the first record was split. For example I get the following results:

Fullname field contains Jim, Smith for the first record
Fullname field contains John, Brown for the second record

After the Update I get the following in the firstname field:

Record 1: Jim
Record 2: Joh (it is missing the "n&quot

Any ideas?

Thanks

 

[Diederik]

This is some strange VFP "by default" behaviour and it has confused more people (just check out this branch...)

When using a function in a SQL..SELECT statement, the length of the first value determines the length/width of the field.

Given your date:
FULLNAME: "Jim, Smith", "John, Brown"
Will result in:
FIRSTNAME: "Jim" (length = space(3))
FIRSTNAME: "Joh" (lenght = still space(3))

To solve this, either create the table beforehand (so that you can set the field-length yourself) or make sure that the function always returns a string of a fixed length, by using the PAD(), PADR() or PADL() functions.

Diederik Vermeeren
verm1864@exact.nl

 

[BlindPete]

Or in your can add FIRSTNAME+SPACE(25) to the expression. ?That is what I do... I then modify the table structure after the fact.

Pete
blindpete@mail.com

What your mother told you is true! You will go blind! (from moonshine anyway)

 

[BobbyT]

This is a little late but it is a routine I have sued for years. You may have yo mofify it but that shouldn't be much of a problem. I may be a lot more than you need.

do blank_um
scan
if name > space(30)
name_work=name
do case
case upper(name_work)="MR " or upper(name_work) ="MR."
per_title="Mr"
name_work=strtran(upper(name_work),"MR &quot
name_work=strtran(upper(name_work),"MR.&quot
name_work=strtran(upper(name_work),"MR. &quot
case upper(name_work)="MRS " or upper(name_work) ="MRS."
per_title="Mrs "
name_work=strtran(upper(name_work),"MRS &quot
name_work=strtran(upper(name_work),"MRS. &quot
case upper(name_work)="MS " or upper(name_work) ="MS."
per_title="Ms"
name_work=strtran(upper(name_work),"MS &quot
name_work=strtran(upper(name_work),"MS. &quot
case upper(name_work)="DR " or upper(name_work) ="DR."
per_title="Dr"
name_work=strtran(upper(name_work),"DR &quot
name_work=strtran(upper(name_work),"DR. &quot
case upper(name_work)="FATH "
per_title="Fath"
name_work=strtran(upper(name_work),"FATH &quot
case upper(name_work)="SIS " or upper(name_work) ="SIST"
per_title="Sis"
name_work=strtran(upper(name_work),"SIS &quot
name_work=strtran(upper(name_work),"SIST &quot
case upper(name_work)="CAPT" or upper(name_work) ="CAP "
per_title="Capt"
name_work=strtran(upper(name_work),"CAPT &quot
name_work=strtran(upper(name_work),"CAP &quot
case upper(name_work)="COL" or upper(name_work) ="COLN"
per_title="Col"
name_work=strtran(upper(name_work),"COL &quot
name_work=strtran(upper(name_work),"COLN &quot
case upper(name_work)="ADM" or upper(name_work) ="ADMR"
per_title="Adm"
name_work=strtran(upper(name_work),"ADM &quot
name_work=strtran(upper(name_work),"ADMR &quot
case upper(name_work)="LT " or upper(name_work) ="LT."
per_title="Lt"
name_work=strtran(upper(name_work),"LT &quot
name_work=strtran(upper(name_work),"LT. &quot
case upper(name_work)="ENS " or upper(name_work) ="ENS."
per_title="Ens"
name_work=strtran(upper(name_work),"ENS &quot
name_work=strtran(upper(name_work),"ENS. &quot
case upper(name_work)="MAJ" or upper(name_work) ="MAJ."
per_title="Maj"
name_work=strtran(upper(name_work),"MAJ &quot
name_work=strtran(upper(name_work),"MAJ. &quot
case upper(name_work)="LCDR" or upper(name_work) ="LCDR."
per_title="Lcdr"
name_work=strtran(upper(name_work),"LCDR &quot
name_work=strtran(upper(name_work),"LCDR. &quot
case upper(name_work)="SGT" or upper(name_work) ="SGT."
per_title="Sgt"
name_work=strtran(upper(name_work),"SGT &quot
name_work=strtran(upper(name_work),"SGT. &quot
endcase
do case
case atc(" MD ",name_work) > 0
name_suff="MD"
name_work=strtran(upper(name_work)," MD &quot
case atc(" DO ",name_work) > 0
name_suff="DO"
name_work=strtran(upper(name_work)," DO &quot
case atc(" OD ",name_work) > 0
name_suff="OD"
name_work=strtran(upper(name_work)," OD &quot
case atc(" OM ",name_work) > 0
name_suff="OM"
name_work=strtran(upper(name_work)," OM &quot
case atc(" ND ",name_work) > 0
name_suff="ND"
name_work=strtran(upper(name_work)," ND &quot
case atc(" DC ",name_work) > 0
name_suff="DC"
name_work=strtran(upper(name_work)," DC &quot
case atc(" EA ",name_work) > 0
name_suff="EA"
name_work=strtran(upper(name_work)," EA &quot
case atc(" CS ",name_work) > 0
name_suff="CS"
name_work=strtran(upper(name_work)," CS &quot
case atc(" DDS ",name_work) > 0
name_suff="DDS"
name_work=strtran(upper(name_work)," DDS &quot
case atc(" DMD ",name_work) > 0
name_suff="DMD"
name_work=strtran(upper(name_work)," DMD &quot
case atc(" DPM ",name_work) > 0
name_suff="DPM"
name_work=strtran(upper(name_work)," DPM &quot
case atc(" LPN ",name_work) > 0
name_suff="LPN"
name_work=strtran(upper(name_work)," LPN &quot
case atc(" RN ",name_work) > 0
name_suff="RN"
name_work=strtran(upper(name_work)," RN &quot
case atc(" LPT ",name_work) > 0
name_suff="LPT"
name_work=strtran(upper(name_work)," LPT &quot
case atc(" PHD ",name_work) > 0
name_suff="PhD"
name_work=strtran(upper(name_work)," PHD &quot
case atc(" ESO ",name_work) > 0
name_suff="ESO"
name_work=strtran(upper(name_work)," ESO &quot
case atc(" CPA ",name_work) > 0
name_suff="CPA"
name_work=strtran(upper(name_work)," CPA &quot
case atc(" MSW ",name_work) > 0
name_suff="MSW"
name_work=strtran(upper(name_work)," MSW &quot
case atc(" CDP ",name_work) > 0
name_suff="CDP"
name_work=strtran(upper(name_work)," CDP &quot
case atc(" CP ",name_work) > 0
name_suff="CP"
name_work=strtran(upper(name_work)," CP &quot
case atc(" II ",name_work) > 0
name_suff="II"
name_work=strtran(upper(name_work)," II &quot
case atc(" III ",name_work) > 0
name_suff="III"
name_work=strtran(upper(name_work)," III &quot
case atc(" JR ",name_work) > 0
name_suff="Jr"
name_work=strtran(upper(name_work)," JR &quot
case atc(" SR ",name_work) > 0
name_suff="Sr"
name_work=strtran(upper(name_work)," SR &quot
ENDCASE
name_work = ltrim(name_work)
if substr(name_work,1,1) > space(1) and (substr(name_work,2,1)=space(1) or;
substr(name_work,2,1) = ".&quot
name_first = substr(name_work,1,1)
name_work = substr(name_work,3)
name_work=ltrim(name_work)
sw_first=.T.
endif
if (substr(name_work,1,1) > space(1)) and (substr(name_work,2,1)=space(1) or;
substr(name_work,2,1) = ".&quot
name_mid = upper(substr(name_work,1,1))
name_work = substr(name_work,3)
name_work=ltrim(name_work)
sw_mid=.T.
endif
cntr_space=rat(" ",rtrim(name_work))
if cntr_space=0
name_last=name_work
else
name_last=ltrim(substr(name_work,cntr_space))
endif
name_work=strtran(name_work,name_last)
if not sw_first
name_work=ltrim(name_work)
cntr2_space=at(" ",name_work)
if cntr2_space=0
name_first=proper(name_work)
else
name_first=substr(name_work,1,cntr2_space)
endif
name_work=strtran(name_work,name_first)
endif
if not sw_mid
name_work=ltrim(name_work)
if at(" ",name_work) > 0
name_mid=upper(substr(name_work,1,1))
endif
endif
name_first=proper(name_first)
name_last=proper(name_last)
*replace salutation with per_title
replace first with name_first
replace init with name_mid
replace last with alltrim(name_last)+" "+name_suff
*replace suff with name_suff
do blank_um
endif
endscan

procedure blank_um
public name_work, name_last, name_first, name_mid, sw_first, sw_mid, per_title, name_suff
name_work = space(40)
name_last = space(18)
name_first = space(12)
name_mid = space(1)
name_suff=space(4)
per_title = space(4)
sw_first = .F.
sw_mid = .F.

 

[Peterout]

Hey thanks guys. I will give these a try and see what the results are. I find that there is a lot to digest using FoxPro. Wish there was software that gave straight forward samples of practical use of expressions and functions. But I will continue reading and testing.

Thanks again.

Peter

 

[dgrewe]

Gee Bobby, Why the big case statements. Just a suggestion but a database would be easier to maintain. Just one mans opinion David W. Grewe
Dave@internationalbid.com
ICQ VFP ActiveList #46145644

 

[Hargreavesk]

Peter,

I understand that you were able to split 1 field into 2 using an update query.

Can you tell me how you did this?

Many thanks

Kate
kate.hargreaves@cis.co.uk

 

[ramani]

HI Kate,
I am not sure if Peter is around ....
Post your field name, type and length... Also specify how you want to split. Probably, we can help you out ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com