Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: sql - phone numbers
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