Solved

Oracle sql Replace

Posted on 2011-09-14
10
319 Views
Last Modified: 2012-08-14
I have one column which needs to use the replace function twice.  I need to remove all comma's and also replace all double quotes with single quotes. The column header needs to be Ballot_Name.  Is it possible to use Replace more than once in a column

((Replace(CANDIDATE_CONTESTS.BALLOT_NAME,',') and   Replace(CANDIDATE_CONTESTS.BALLOT_NAME,'""', '''') )Ballot_Name),
0
Comment
Question by:restockett
10 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36537227
Yes:  nest the replaces:

Replace(replace(CANDIDATE_CONTESTS.BALLOT_NAME,'"",'"'),',')
0
 

Author Comment

by:restockett
ID: 36537294
I tried this and got the ORA-00972 Identifier is to long error.  Message.  Can this be fixed.  How do i tell the query to use BALLOT_NAME as the column header.  would that make it work?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36537310
>>tell the query to use BALLOT_NAME as the column header

do you mean column aliases?

Replace(replace(CANDIDATE_CONTESTS.BALLOT_NAME,'"",'"'),',') as BALLOT_NAME

Note the 'as' is optional in Oracle.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:restockett
ID: 36537343
Yeah, i tried that but still got the error message with and without the 'as'.  What it is trying to do if i dont put the alias is use CANDIDATE_CONTESTS.BALLOT_NAME,'"",'"'),',' as the column header I think.  Even if i dont use an alias i get the same error.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36537361
What program/product/??? are you using with the headers?
0
 

Author Comment

by:restockett
ID: 36537378
I am using TOAD (vs 8+) to write the query for Oracle sql.  Is that what you mean by your question.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36537421
Toad 'should' recognize the column alias but I'm not a Toad user.

Try the nested replaces in sqlplus.  It will work if I don't have a simple typo in there somewhere.
0
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 36537594
You do have a typo (missing a single quote).

Replace(replace(CANDIDATE_CONTESTS.BALLOT_NAME,'""','"'),',') as BALLOT_NAME


Is that really what you want?

The way I read the question, what you want is this:

Replace(replace(CANDIDATE_CONTESTS.BALLOT_NAME,'"',''''),',') as BALLOT_NAME
0
 

Author Comment

by:restockett
ID: 36554740
I am getting the results I wanted with it written the way I said above.  Gotta go.  Moving on to other problems.
0
 
LVL 32

Expert Comment

by:awking00
ID: 36575067
You can also use translate -
translate(ballot_name,'",',chr(39))
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now