Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Oracle sql Replace

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
restockett
Asked:
restockett
1 Solution
 
slightwv (䄆 Netminder) Commented:
Yes:  nest the replaces:

Replace(replace(CANDIDATE_CONTESTS.BALLOT_NAME,'"",'"'),',')
0
 
restockettAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
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.

 
restockettAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
What program/product/??? are you using with the headers?
0
 
restockettAuthor Commented:
I am using TOAD (vs 8+) to write the query for Oracle sql.  Is that what you mean by your question.
0
 
slightwv (䄆 Netminder) Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
restockettAuthor Commented:
I am getting the results I wanted with it written the way I said above.  Gotta go.  Moving on to other problems.
0
 
awking00Commented:
You can also use translate -
translate(ballot_name,'",',chr(39))
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now