an easy way to filter these

[gfrlaser]

SANTA BARBARA CA 93108 1048

Most have the '-' (dash) included in the zipcode. What simple statement will filter those that have no dash between the zip and the zip+4? Thanks.

 

[JRB-Bldr]

There are a variety of ways to differentiate the zipcodes.
And you can either FILTER the results in the existing table or INDEX on the results or put the results into a Cursor/Table with a SQL Query

SELECT Zipcode FROM MyTable WHERE "-" $ zipcode
SELECT Zipcode FROM MyTable WHERE LEN(ALLTRIM(Zipcode)) > 5

Good Luck
JRB-Bldr

 

[gfrlaser]

Ah, however, thats a problem, the citystatezip is in the same field! I just need to find that ones that have a zip+4 on it, but no -. Mail Manager will not split the field for me if there is no dash.

Make sense?

 

[Dan Freeman]

ATC("-",zipcodefield)=0 is the condition you're looking for, but there are various ways to express it or bring the result you need.

Can you elaborate on "filter those that"? The phrase itself can have many meanings depending on the task at hand.

 

[gfrlaser]

i just want to filter them, then eliminate the last four digits.

 

[gfrlaser]

here. I need to filter this... this is one field.

SANTA BARBARA CA 93108 1048

I only want the 5 digit left. After that, I am fine.

 

[gfrlaser]

Actually, I just want to eliminate the last 4 digits.

 

[JRB-Bldr]

mcCityStateZip = 'SANTA BARBARA CA  93108 1048'
DO CASE
   CASE '-' $ mcCityStateZip
      * --- Dash between Zip & Zip4 ---
      mnLoc = RAT("-",mcCityStateZip)
      mcCityStateZip = LEFT(mcCityStateZip,mnLoc-1)
   
   CASE SUBSTR(mcCityStateZip,LEN(mcCityStateZip)-4,1) = " "
      * --- Space between Zip & Zip4 ---
      mnLoc = RAT(" ",mcCityStateZip)
      mcCityStateZip = LEFT(mcCityStateZip,mnLoc-1)

   OTHERWISE
      * --- No Zip4 - Leave Alone ---
ENDCASE
mcZipcode = RIGHT(mcCityStateZip,5)

If the above is on the right track, you can work it into a single SQL Query for all records or just do a SCAN/ENDSCAN of the original table to build a resultant cursor/table.

Good Luck
JRB-Bldr

 

[Dan Freeman]

OK, you've now asked for three different "filters" but we still have no clue how you want them applied or in what operation.

Are you trying to SET FILTER TO? (I suspect not.)

It sounds like you're looking for a report expression which actually has nothing whatsoever to do with filtering.

What ARE you trying to do?

 

[gfrlaser]

I just want to get rid of the last 4 digits, if there are any from the field citystzip. If it contains 45689 5804 at the end of the field, I want 5804 gone. All the other records only have 5 digits at the end anyway, and are ok as is. I just thought that filtering them first would be easiest. I am sure there is an easy way to do this. sorry if I confused the issue.

 

[JRB-Bldr]

Test the statements that I have given you above.
They should do what you want.

You can merge those commands above into a single FILTER statement.
Or merge them into an INDEX expression.

(HINT - try putting them into an IIF() expression)

Just work with the code....

Good Luck,
JRB-Bldr

 

[TheRambler]

Try the following:

USE yourTable
SCAN
   lnLength = LEN(RTRIM(yourField))
   lnSpacePosition = RAT(CHR(32), RTRIM(yourField))
   IF lnLength - lnSpacePosition = 4
      ? yourField
      ? LEFT(yourField, lnSpacePosition - 1)
   ENDIF
ENDSCAN

 

[Olaf Doschke]

If there can't be any other cases that the last four chars (of the rtrimmed field value) are those 4 digits of the zip+4 codes TheRambler has the solution, except you might also want to check for '-' instead of chr(32).

You could additionally check if those 4 chars after the last space are digits with ISDIGIT() and check, if it's just one space seperating the numbers and if the 5 chars in front of the single space are digits too, to be even surer about not deleting something else.

One more general way would be using regular expressions. Here's one example to find zip+4 codes with regexp:

http://blogs.msdn.com/ericgu/archive/2005/11/11/491938.aspx

oRE = CreateObject("VBScript.RegExp")
oRE.Pattern="\d{5}[-| ]\d{4}"
Set Filter To oRE.Test(citystzip)

See the discussion in the blog to improve the expression to not find something like 12345-12345 or other invalid zip+4 codes.

Bye, Olaf.

 

[gfrlaser]

Thanks to all for the help. Rambler, your code worked perfect for me! I hate when files come in with the city, the state and the zip are all in one field. Usually this is not a problem except for the missing hyphen. But problem solved now. Thanks again.

 

[cricket]

You can extend your code a little further.
You should be able to validate your extracted ZIP codes against a national table of ZIP codes.
You could validate the city against the national table of ZIP codes for that city area.

 

[bojammy]

Sounds like you are getting data from an external source and need to normalize it. The previous replies are good stuff. I'm in the Goleta. Fun to hear there is another FoxPro programmer in the neighborhood

 

[gfrlaser]

The Goleta?