Solved

Oracle sql Replace

Posted on 2011-09-14
10
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 77

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 77

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 77

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 77

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 35

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

749 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