Robelle shows off uniformizing phone data
June 24, 2014
The latest newsletter from Robelle Solutions Technology shows off how to normalize phone numbers in databases. (To be precise, this is a process that's different from classic database normalization: It's more like "uniformization," to cobble together a term, since normalization has already been taken, years ago while creating database maintenance procedures.)
The object of this uniformization is to remove the non-number characters from a phone number byte container. Normalization is a significant element in data cleansing. As IT pros on the move in a migration, or just diligent about their use of company resources will report, cleansing doesn't happen only when you're moving data between platforms or app to app.
Suprtool expert Neil Armstrong of Robelle said that "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.
Robelle -- whose Bob Green also posted news of this month's HP3000 Reunion meeting at Dirty Dick's pub in London -- asked Armstrong to show how all of these phone formats could be fit into a consistent container.
"The steps in normalizing the data are to remove the non-numeric numbers," Armstrong said in his article.
>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.