Re: sql - phone numbers

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 16 Oct 1999 16:27:45 GMT
Message-ID: <7ua921$379$7@news.seed.net.tw>

Gregory Caulton <gcaulton_at_sympatico.ca> wrote in message news:3805FDE7.776A97F9_at_sympatico.ca...
> 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.

An interesting puzzle.
It can be done by using TRANSLATE() function twice to distill digits:

SQL> set numwidth 12
SQL> create table phone(no varchar2(20));

Table created.

SQL> insert into phone values ('123-456 7890');

1 row created.

SQL> insert into phone values ('(234)234-1234');

1 row created.

SQL> insert into phone values ('345/345/3456');

1 row created.

SQL> insert into phone values ('555-555-5555');

1 row created.

SQL> insert into phone values ('666x666x6666');

1 row created.

SQL> select * from phone;

NO

---

123-456 7890
(234)234-1234
345/345/3456
555-555-5555
666x666x6666

SQL> select to_number(translate(no,
  2 '0'||translate(no, ' 0123456789', ' '), '0')) as no   3 from phone;

          NO

---

  1234567890
  2342341234
  3453453456
  5555555555
  6666666666 Received on Sat Oct 16 1999 - 11:27:45 CDT