Parsing CUST_PHONE

[bobby]

HI,
i have a source staging and DM My req are like this in
DIM_JOB STG_JOB
Cust_phone cust_phone
ptn_area_cd parse cust_phone -1st three chracter area cde
ptn_npa parse cust_phone three chracter starting at 4th
digit and insert first 3 chracter of phone no.


ptn_npa_nxx parse cust_phone-last 4 chracter of the phone
number

(Phone number in source is like 888-888-8888)
if u can guide me in detail i will be grateful.
Thanks,
bob

[chulett]

Bob - this isn't an IM client, so it's ok to use whole words and complete sentences. Besides, in an international forum it would help people understand your questions - especially those for whom English is not their native tongue.

Now, to the subject of parsing a phone number. Check your online help for the Field function. It is used to pull pieces from a delimited string like a phone number. So, you could use:

Code: Select all

Field(cust_phone,"-",1) to get ptn_area_cd
Field(cust_phone,"-",2) to get ptn_pna
Field(cust_phone,"-",3) to get ptn_npa_nxx

There are other options for the function, for example you could pull more than one consecutive field at a time, but this basic syntax will work for you.

[ray.wurlod]

So easy in the USA, where every phone number has the same format. Probably the most efficient, though least flexible, is substrings.
Here are some variations on that theme.

Code: Select all

cust_phone[1,3]                       Left(cust_phone,3)
cust_phone[5,3]
cust_phone[9,4]     cust_phone[4]     Right(cust_phone,4)

The Field function, which Craig posted, is fairly efficient too, because the underlying engine makes immense use of this function for unpacking its own data.

Contemplate a job I had last year for Indian phone numbers, where the area code can be between 2 and 5 digits, but had to be validated, and as quickly as possible. The remainder of the telephone number (which still had to be parsed into carrier and so on) parsing rules depended on lengths that in turn depended on the length of the area code. This in a country of over 1,000,000,000 people, many of whom make phone calls.

[bobby]

hi actually the source is 3133456784
will the same field work there too
thanks
bob

[chucksmith]

No. The field function requires delimiters, as the documentation says. Ray's suggestion, using substrings, is probably your best approach.