receive scf counts from a 5 digit zip field 2

[learnfox2]

How do I copy the first 3 digits of a zipcode into a new field called "scf", run a unique count on the scf's in the new field, just created

 

[dgrewe]

Assumptions the field is already in the table

USE ZIPCODE exclu
replace all SCF with SUBSTR(ZIPCODE,1,3)
index on SDF TAG USCF UNIQUE
set order to USCF
count to lnCnt
? lnCnt

or

do it in a Select statement to a cursor and do a record count when your done.



David W. Grewe Dave

 

[bborissov]

UPDATE ZipTable SET SCF = LEFT(ZipCode,3)
SELECT COUNT(DISTINCT SCF) FROM ZipTable INTO CURSOR crsTest
BROWSE NORMAL

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

 

[Imaginecorp]

The above 2 examples assume the field is a Character Field, If Numeric you will have to convert to character first and also add an Alltrim() if the field size is larger than the zip code
***
z = 12211
? left(alltrim(str(z)),3)
? substr(alltrim(z),1,3)

 

[Imaginecorp]

sorry:
? substr(alltrim(z),1,3)
should be
? substr(alltrim(str(z)),1,3)

 

[baltman]

It should be a character as there are leading zeros. Either way TRANSFORM is great because it doesn't care.

LEFT(PADL(TRANSFORM(ZIP),5," "),3)

If you have a mix of zip and zip+4 you need to remove the "-" with chrtran() and then PADL to 9 if LEN(ALLTR(TRANS(ZIP)))>5.

Brian

 

[mmerlinn]

I don't see how PADL(ZIP, 5, " ") does anything except provide a Sectional Center Facility list that includes many SCFs with leading spaces as valid SCFs, which of course is impossible since all SCFs must consist of exactly 3 numerical digits.

Any Zip Code list that is not a Character field will produce erroneous results unless the leading zeroes for PR, VI, MA, RI, CT, ME, NH, VT, and NY (APO's, FPO's, Reader's Digest, & IRS) are added back in with extra coding. In reality I see no reason to ever store Zips in anything but a Character field. No math is ever used with them. All manipulation is with Character based functions.

When splitting out the SCFs, the length of the zip does not matter as all that is wanted is the first three digits. Because of this, I see no reason to pad anything so long as all zips are at least 5 digits long. Those Zips should have already been validated to this length upon original entry. If they are incorrect at this point, then the more important issue is getting that problem fixed first to avoid future issues.

Nor do I see any reason to worry about the '-' since it is always in position 6 or not used at all.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[baltman]

BRISTOL MA = "02031" if stored as a number = 2031.

LEFT(PADL(TRANSFORM(2031), 5, " "),3) = " 20"
LEFT(PADL(TRANSFORM(2031), 5, "0"),3) = "020"
LEFT(PADL(TRANSFORM("2031"), 5, " "),3) = " 20"
LEFT(PADL(TRANSFORM("2031"), 5, "0"),3) = "020"

cZip5_4 = "2031-5555"

?LEFT(PADL(TRANSFORM(CHRTRAN(cZip5_4,"-","")), IIF(LEN(ALLTRIM(TRANSFORM(CHRTRAN(cZip5_4,"-",""))))<=5,5,9), "0"),3)


cZip5_4 = "02031-5555"

?LEFT(PADL(TRANSFORM(CHRTRAN(cZip5_4,"-","")), IIF(LEN(ALLTRIM(TRANSFORM(CHRTRAN(cZip5_4,"-",""))))<=5,5,9), "0"),3)

cZip5_4 = "020315555"

?LEFT(PADL(TRANSFORM(CHRTRAN(cZip5_4,"-","")), IIF(LEN(ALLTRIM(TRANSFORM(CHRTRAN(cZip5_4,"-",""))))<=5,5,9), "0"),3)

cZip5_4 = "20315555"

?LEFT(PADL(TRANSFORM(CHRTRAN(cZip5_4,"-","")), IIF(LEN(ALLTRIM(TRANSFORM(CHRTRAN(cZip5_4,"-",""))))<=5,5,9), "0"),3)

 

[baltman]

Per the thought that validation and field design should be a given... that's nice. But we don't get to choose if we have normalized and validated data. Sometimes we have to make it make it. Sorry I padded with a chr(32) when a chr(48) was called for.

Brian

 

[Imaginecorp]

Thank You. Glad I could help...