Solved

Oracle sql Replace

Posted on 2011-09-14
10
327 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.
Suggested Courses

630 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