satish

• Oct 23rd, 2006

 

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)

Arun

• Oct 23rd, 2006

 

Answer :

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

Murugan

• Oct 24th, 2006

 

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

dev

• Oct 24th, 2006

 

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.

dev

• Oct 24th, 2006

 

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

Swapna Gentyala

 Profile 

• Oct 30th, 2006

 

Hi you can try this query to update the tableupdate phone set phone2 = "(" & left(phone.phonenumber,3) & ")" & right(phone.phonenumber,7)

ss_peri

 Profile Answers by ss_peri 

• Mar 4th, 2007

 

Hi,
You can use the concept of External Tables. These external tables reads the DATA/FLAT File. And now you can read the External table as an ordinary oracle table, and do the data manipulation, and insert into into phone2 table.

paresh Borad

• Aug 1st, 2011

 

Use this query

create table newphone as (select '(' || substr(phnumber, 1,3) || ')' || substr(phnumber,4,10) "phonenumber" from oldphone)

SURESHMALLIPEDDI

 Profile Answers by SURESHMALLIPEDDI 

• Aug 9th, 2011

 

select '('||substr(phone,1,3)||')'||substr(phone,4) "formated phone number",phone from phone