| Update Data Script -- Help !!! [message #19648] |
Tue, 02 April 2002 10:33  |
Arpit
Messages: 99
Registered: March 2001
|
Member |
|
|
Hello ,
Can someone help me with my update script. I need to update the phone field in the employee table. The data this field has is +1 (416) 666-1234. How could i update it to (416) 666-1234. I need to get rid of +1 along with a single space.
Any help on this would be highly appreciated.
Thanks!
Alpesh
|
|
|
|
|
|
|
|
| Re: Update Data Script -- Help !!! [message #19672 is a reply to message #19648] |
Wed, 03 April 2002 07:23  |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
To_PHONE function:
create or replace function to_phone(phonein number, p_format varchar2)
return varchar2 is
phoneout varchar2(20) default '';
vlen number;
cnt pls_integer default 0;
cnt2 pls_integer default 0;
begin
vlen := length(p_format);
for i in 1..vlen loop
cnt := cnt + 1;
if substr(p_format, cnt, 1) <> '9' then
phoneout := phoneout || substr(p_format, cnt, 1);
cnt2 := cnt2 + 1;
else phoneout := phoneout || substr(phonein, (cnt - cnt2), 1);
end if;
end loop;
return phoneout;
end;
/
@to_phone.sql
SQL> create table t (t_phone varchar2(15));
Table created.
SQL> desc t
Name Null? Type
----------------------------------------- -------- -----------------
T_PHONE VARCHAR2(15)
SQL>insert into t values ('+1(777)234-8889');
Before update in t table:
SQL> select * from t;
T_PHONE
---------------
+1(777)234-8889
SQL> update t
2 set t_phone = to_phone(18887772222, '9(999) 999-9999');
1 row updated.
SQL> commit;
Commit complete.
After update t table:
SQL> select * from t;
T_PHONE
---------------
1(888) 777-2222
To_phone function will take convert any format. Check your columns name and don't modify any codes in the to_phone function.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
