Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Format Phone Numbers

10211Jul 19 2005 — edited Mar 16 2007
I would like to store all phone numbers as 1112223333.
How can I strip out all other characters (ex. ()-) prior to inserting in table?

I created the following however, if someone insertst a space (ex. (212) 555-1212) it does not work:

declare
v_phone varchar2(35);
begin
v_phone := '(212)555-1212';

for i in 1..length(v_phone) loop
if substr(v_phone,i,1) not in ('0','1','2','3','4','5','6','7','8','9') then
select replace(v_phone,substr(v_phone,i,1),'') into v_phone from dual;
end if;
end loop;
dbms_output.put_line(v_phone);
end;

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 13 2007
Added on Jul 19 2005
11 comments
5,782 views