Hi Guys,i have an interview question. can anyone help me reg this.i have a table called "phone" and filed as "phone number" and table have values like 2489078905,2345678909now i want the result as (248)9078905,(234)5678909 into another table called phone2.

Showing Answers 1 - 15 of 15 Answers

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)

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Swapna Gentyala

  • Oct 30th, 2006
 

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

paresh Borad

  • Aug 1st, 2011
 

Use this query

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

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions