Split a field 3

[learnfox2]

Using Visual Foxpro how can I remove all items after a comma in one field to another? For example. In my address
field I have "123 Main Street , Apartment 2B" I want to move the Apartment 2B into another new field. How can I do this for more than one record at a time?
Help is greatly appreciated.

 

[bborissov]

UPDATE YourTable SET NewField = SUBSTR(OldField, AT([,], OldField)+1) WHERE AT([,], OldField) # 0

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DSummZZZ]

This too is untested, but you can do something like:

SCAN
   STORE ALLTRIM(LEFT(address, AT(',', address)-1)) TO cTempAddress1
   STORE ALLTRIM(SUBSTR(address, AT(',', address)+1)) TO cTempAddress2
   REPLACE address WITH cTempAddress1
   REPLACE newfield WITH cTempAddress2
ENDSCAN

Of course, after making a backup.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Imaginecorp]

It is not that simple....

The Very First step is to determine if every address field has the address in the same format, i.e. "123 Main Street , Apartment 2B", Is there always an "Apartment" in the address? Is there always a comma between them? In some cases it could be spelled "Apt 2B". Is the street always before the apartment i.e. Apt 2B, 123 Main Street.

There is No simplistic way to do this... You need to do multiple runs to do this right

 

[Imaginecorp]

You will need to do something like this.

****tablename = test
****address field = address
****apartment = apt_address
Select test
Scan
	If Occurs(",",test.address) > 0 And ;
			INLIST(Upper(test.address),"APT","APARTMENT")
		****if apartment number is before the street name
		If "APARTMENT" $ Substr(Upper(test.address),1,(At(",",test.address)-1)) Or ;
				"APT" $ Substr(Upper(test.address),1,(At(",",test.address)-1))
			***move the apartment to a different field
			***and rewrite the old field with the street
			Replace test.apt_address With Strextract(test.address,"",","),;
				test.address With Strextract(test.address,",")
		Endif
		****if apartment number is after the street name
		If  "APARTMENT" $ Substr(Upper(test.address),(At(",",test.address)+1)) Or ;
				"APT" $ Substr(Upper(test.address),(At(",",test.address)+1))
			***move the apartment to a different field
			***and rewrite the old field with the street
			Replace test.apt_address With Strextract(test.address,","),;
				test.address With Strextract(test.address,"",",")
		Endif
	Endif
Endscan

Again this is to give you an idea...you will have to fine tune

 

[AndyKr]

There are lots of ways of handling this in VFP, others have shown some different ones, but perhaps the easiest is to use the STREXTRACT() fucntion introduced in Version 7.0.

You can SCAN your table and for each record use code like this:

*** Get everything from first comma
lcRemove = STREXTRACT( address, ",", "", 3)
IF NOT EMPTY( lcRemove )
*** Remove it from the original field
REPLACE address WITH STRTRAN( address, lcRemove, '' )
*** Add it to the new field
REPLACE newefield WITH lcRemove
ENDIF

----
Andy Kramek
Visual FoxPro MVP

 

[Imaginecorp]

Hello Andy; nice to see you here.

I am assuming there is no separate field in the form for the user to enter the apartment #, thus the question, I have found in a scenario like this the user input is unpredictable. They may enter the apartment # before the street or vice versa or no commas as separators etc etc…

I think the poster needs to check how the address is inputted first prior to any manipulation…

 

[Imaginecorp]

Thank You. Glad I could help.

 

[AndyKr]

>>I am assuming there is no separate field in the form for the user to enter the apartment #, thus the question, I have found in a scenario like this the user input is unpredictable. They may enter the apartment # before the street or vice versa or no commas as separators etc etc…

Definitely! In fact the simplest solution is to use a proper address verification program (there are several available) that will correctly sort out precisely these issues. They can be quite expensive, admittedly, but if it is important then it is definitely the only way to go.

Generally it is not too hard to identify the City, State and Zip and with that data the remainder (the street address) can usually be determined pretty accurately.

One of our clients uses AccuMail - it has a nice COM component and works well with VFP. It is fast and accurate and we use it to process hundreds of thousands of addresses from a wide variety of sources (some look like they have been scraped off web pages!) with no consistent format or rules but we get better than 95% matching with Accumail.

----
Andy Kramek
Visual FoxPro MVP

 

[mmerlinn]

[ ]
I would do it something like this using UDFs (FAQ184-5975):


SCAN
cTempLeft = ATXLEFT(Oldfield, ",")
cTempRight = LTRIM(ATXRIGHT(Oldfield, ","))
IF "APT" $ UPPER(cTempLeft) OR "APARTMENT" $ UPPER(cTempLeft)
REPLACE Newfield WITH cTempLeft
REPLACE Address WITH cTempRight
ELSE
REPLACE Newfield WITH cTempRight
REPLACE Address WITH cTempLeft
ENDIF
ENDSCAN


UNTESTED!

This assumes that a comma delimits the address parts. If any other delimiter is used, you may have surprising results. If more than one comma is in the original, address is broken at the FIRST comma. Comma is discarded. If there is no comma, nothing is changed, except for the addition of a blank new field.

Also assumes that "APT" and "APARTMENT" are the only possible qualifiers. Will fail with things like "#2C" and "Suite 2C" and addresses that contain apartment info before and after the comma (It happens).

If "APT" or "APARTMENT" is either before OR after the comma this will place the apartment info in the new field and rest of address in original field (BACKUP ORIGINAL FIRST!)

Otherwise if apartment info does not exist, original field will remain unchanged and new field will contain nothing.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

[ ]

Oops. Just realized that '"Oldfield" and "Address" are the same variable and should not have two different names. So, change "REPLACE Address TO ..." with "REPLACE Oldfield TO ..."


mmerlinn

"Political correctness is the BADGE of a COWARD!"