Help in query

I have a tableA in that a column having the data like this

tableA
======
col1
"Consumer Reports","Direct mail","Internet search"
"Direct mail","Home improvement TV show"
"Demonstration in store","Manufacturers website","Direct mail"

I want to load this data into another table with out having double quotes

tableB
======
col1
Consumer Reports,Direct mail,Internet search
Directmail,Home improvement TV show
Demonstration in store,Manufacturers website,Direct mail

Please help me in advance. How can i achieve this.

Thanks
leelakiranAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Try:
Insert into tableB(col1) (select replace(col1,'"',null) from tableA);
0
 
leelakiranAuthor Commented:
Thanks slightwv.

But i have doubt the same i use anonymous block.its getting error.

declare
optflag varchar2(100);
begin
optflag:=replace("Consumer Reports","Direct mail","Internet search",'"',null);
dbms_output.put_line('the value is' || optflag);
end;
/

how can i achieve this.

Please help me in advance
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Replace takes a string literal in oracle:

optflag:=replace('"Consumer Reports","Direct mail","Internet search"','"',null);
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
kambleamarCommented:
Hello Kiran,

There are 2 mistakes in the query
you can have 2 two or three  parameters in the Replace function, but u cannot  have more then 3  parameters

replace( string1, string_to_replace, [ replacement_string ] )

also the replace("Consumer Reports","Direct mail","Internet search",'"',null);
should be
replace('Consumer Reports','Direct mail','Internet search','"',null);
in single quotes


0
 
slightwv (䄆 Netminder) Commented:
kambleamar,

Your replace is incorrect as well.
0
 
awking00Connect With a Mentor Commented:
select translate(col1,'x'||chr(34),'x') from tableA;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.