Normalizing Zip Codes

Fix all of My Zip codes

Recently a customer asked how they could fix some of the Zip codes which were in the form of:

	123450000

and are stored in a numeric J2 field.

The customer wanted to normalize those zip codes that had the four trailing zeroes to be 12345 instead of 123450000.

The first step we wanted to do was to determine all of the codes that needed to be updated and what the old zip would be and what the new zip would be.

 >base membrs
 >get member-file
 >def new-zip,1,4,double
 >if zip > 99999 and (zip mod 10000) = 0
 >ext account
 >ext zip
 >ext new-zip = zip / 10000
 >list
 >xeq

So what is the above doing?

Well the if command looks at all records that are greater than 99999 and ends in the four zeroes, which is what the (zip mod 10000) = 0 is doing. This should isolate just those records that the customer wanted to fix!

Once we determined that we had the correct records selected we easily updated them with:

 >base membrs
 >get member-file
 >if zip > 99999 and (zip mod 10000) = 0
 >update
 >ext new-zip = zip / 10000
 >list
 >xeq

Normalizing Data

How do I Normalize Phone Numbers?

Recent question about Phone Numbers and how to remove non-number characters from a byte container, raised some interesting solutions to normalizing phone numbers:

Considering the following data, you see that the phone numbers have all sorts of different formats.

>in myphone
>list
>xeq
>IN myphone (0) >OUT $NULL (0)
PHONENUM        = #123.456.7890

>IN myphone (1) >OUT $NULL (1)
PHONENUM        = (123)567-1234

>IN myphone (2) >OUT $NULL (2)
PHONENUM        = (321).123.5678

IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1.

The steps in normalizing the data is to remove the non-numeric numbers:

>in myphone
>set cleanchar ""
>clean "^0:^47","^58:^255"
>def newphone,1,14
>ext phonenum=$clean(phonenum)
>out newphone,link
>xeq
IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1.

>in newphone
>list
>xeq
>IN newphone (0) >OUT $NULL (0)
PHONENUM        = 1234567890

>IN newphone (1) >OUT $NULL (1)
PHONENUM        = 1235671234

>IN newphone (2) >OUT $NULL (2)
PHONENUM        = 3211235678

IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1.

You can then use an edit mask to format it in the same way. You do need to redefine the field being edited with a define of the number with just the length of the phone number:

>in newphone
>form
    File: newphone     (SD Version B.00.00)  Has linefeeds
       Entry:                     Offset
          PHONENUM             X14     1
    Entry Length: 14  Blocking: 1
>def my,phonenum,10
>def targ,1,12
>ext targ=$edit(my,"xxx.xxx.xxxx")
>list
>xeq
>IN newphone (0) >OUT $NULL (0)
TARG            = 123.456.7890

>IN newphone (1) >OUT $NULL (1)
TARG            = 123.567.1234

>IN newphone (2) >OUT $NULL (2)
TARG            = 321.123.5678

IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1.