?
Solved

Help in query

Posted on 2011-04-19
8
Medium Priority
?
218 Views
Last Modified: 2012-12-19
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
0
Comment
Question by:leelakiran
6 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1336 total points
ID: 35423550
Try:
Insert into tableB(col1) (select replace(col1,'"',null) from tableA);
0
 

Author Comment

by:leelakiran
ID: 35423702
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 total points
ID: 35424026
Replace takes a string literal in oracle:

optflag:=replace('"Consumer Reports","Direct mail","Internet search"','"',null);
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 2

Expert Comment

by:kambleamar
ID: 35424033
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35424050
kambleamar,

Your replace is incorrect as well.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 664 total points
ID: 35427283
select translate(col1,'x'||chr(34),'x') from tableA;
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question