Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql - phone numbers

Re: sql - phone numbers

From: Lothar Armbrüster <lothar.armbruester_at_rheingau.netsurf.de>
Date: 14 Oct 99 21:26:39 +0100
Message-ID: <962.956T1995T12864586@rheingau.netsurf.de>


Gregory Caulton wrote at 14-Okt-99 17:02:11 on the subject sql - phone numbers:

>If I have a column with phone numbers in a variety of formats
> e.g. 555-555 5555
> (555)555-5555
> 5555555555
>and what it to insert into a column that is numeric i.e. I need to
>change all of them to a number, what is the best way, I seem to get
>'invalid number' often when trying to mix to_number, translate and nvl
>together into my insert.

I would try something like

to_number(translate(phone,'0123456789(-/','0123456789'))

You will have to add any character to the first string which may occur in your numbers.
To find out which this are you could do something like

select distinct translate(phone,'x01234567890','x') from ...

>Greg

Hope that helps,
Lothar

--
Neue Adresse ab 10.07.'99:

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester_at_t-online.de
D-65346 Eltville        |

Received on Thu Oct 14 1999 - 15:26:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US