Re: Parsing a phone number

This looks like a job for PL/SQL. Basically, here is what I would do:

• Read the table with phone numbers in free form. Use a SELECT statement like this: SELECT phone_number, length(phone_number) phone_length, rowid FROM PHONE_NUMBER_TABLE;
• Next, I would go through a loop that would start from 1 and go to phone_length.
• Each time through the loop, I would get the nth character from the phone_number using the substr function. If this character is 0 through 9, I would append it to a variable storing the stripped out phone number; otherwise, I would discard the character.
• Once doen with the loop, update the phone_number to the new variable value based on the rowid.

Hope this makes sense.

Dave Grzebien
ETCI
dave_at_etci.net

steveee_ca_at_my-deja.com wrote:

> Hi everyone,
>
> I've just been asked to create a script that will go through a field of
> phone numbers that have been stored 'free form' (e.g. (614) 222-3333,
> 614-222-3333, 222-3333 etc) and strip out all of the punctuation and
> spaces
> so that only the numerals are stored (i.e. 6142223333).
>
> This is a one time only thing so I don't have to be too worried about
> performance..we'll run it at 2 am if we have to :)
>
> Any help would be deeply appreciated..
>
> Oracle 8.0 , Sun Solaris 2.6
>
> Thanx in advance,
>
> Steve
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Nov 16 2000 - 19:26:39 CST