insert into  phone2 (phoneno)

select '('+ left('phone number',3)+')'+right('phone number',7) from phone

Another simple example is....execute this directly and see the result.

select '('+ left('2489078905',3)+')'+right('2489078905',7)

Answer :

insert into phone2 (select '('||substr(phoneno,1,3)||')'||substr(phoneno,4)  from phone)

Hi,

     We need to use PL/SQL with explicity cursors. The following anonyms block will work,

declare
ph_num phone.phone_number%type;
ph_num_1 phone2.phone_number%type;
cursor ph_num_2 is select phone_number from phone;
begin
open ph_num_2;
loop
fetch ph_num_2 into ph_num;
exit when ph_num_2%notfound;
ph_num_1 := '(' || substr(ph_num,1,3) || ')' || substr(ph_num,4,10);
insert into phone2(phone_number) values(ph_num_1);
end loop;
close ph_num_2;
end;
/

one thing is important, column phone_number in phone2 should be in varchar2 not in number.

Regards,

M.Murugan

Hi All,

Thank u for ur responses and i have one more adjustment in the question like:

Those phone numbers are present in one file not in table. table is also there but it is empty.

File Name is:Data

in this file , we have phone no's like 2489078905,2345678909 and i have an empty table phone with phoneno as a field. i want the results in Phoen2 table with format like this,(248)9078905,(234)5678909.

Hi all,

thank u for ur answers.and i have an adgustment in this question.

actually data is not present in a table, it's there in some file.say, "Data" contains information: 2489078905,2345678909 and so on...
and table is also there phone with phoneno as a field but table is empty.now i want the result into table2.

got it, Right!

Let me know if u have any questions.

thanks.

dev