Solved

Oracle sql Replace

Posted on 2011-09-14
10
316 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)
Comment Utility
Yes:  nest the replaces:

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

Author Comment

by:restockett
Comment Utility
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)
Comment Utility
>>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
 

Author Comment

by:restockett
Comment Utility
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)
Comment Utility
What program/product/??? are you using with the headers?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:restockett
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Comment Utility
I am getting the results I wanted with it written the way I said above.  Gotta go.  Moving on to other problems.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 41
complicated query 15 41
how to fix this error 14 46
Action link in Union Reports Not Working in OBIEE 11g 1 19
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 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

17 Experts available now in Live!

Get 1:1 Help Now