• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

how to replace the double quote or single quote at the same time?

I am facing a problem during select and replace from oracle... i need to replace double quote and single quote to empty

How to add into the select statement? i only know add in one ' " '
 how to add in 2 condition?
SET FEEDBACK OFF
                SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
                SET LINESIZE 32767
                SET DEFINE OFF
                SET TRIMOUT ON
                SET HEADING OFF

                SELECT BUF FROM (
                SELECT ROWNUM SEQ, t.BUF
                FROM (
                        SELECT
                                'Data^B' || REPLACE(UPPER(ttttt), '"') || '^B'
                                || REPLACE(aaaaa, '"') || '^B'
                                || REPLACE(bbbbb, '"')|| '^B'
                                || REPLACE(ccccc, '"')|| '^B'
                                || REPLACE(ddddd, '"')|| '^B'
                                || REPLACE(eeeee, '"')|| '^B'
                                || USECOUNT
                                BUF
                        FROM NAME
                        WHERE
                                aaaaa LIKE '$a%'
                                AND bbbbb LIKE '$b%'
                                AND ccccc LIKE '$c%'
                                AND eeeee LIKE '$d%'
                                AND fffff = 'dd'
                                ORDER BY USECOUNT DESC
                ) t
                WHERE ROWNUM < $RLIMIT ) s
                WHERE s.SEQ >= $PSTART;

Open in new window

0
gagajanice
Asked:
gagajanice
2 Solutions
 
dqmqCommented:
I don't quite understand the code  you provided as REPLACE requires 3 arguments and you only provide 2.  That said, you can do a multiple replace, either by nesting the replace functions or by using the translate function:

REPLACE(REPLACE(aaaaa, '"',''),'''','')

or

TRANSLATE(aaaaa,'"''','')
0
 
Helena Markováprogrammer-analystCommented:
Here is an example:
select replace(REPLACE('a''a"a', '"'),'''') from dual;

I hope I have understood question :)
SET FEEDBACK OFF
                SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
                SET LINESIZE 32767
                SET DEFINE OFF
                SET TRIMOUT ON
                SET HEADING OFF

                SELECT BUF FROM (
                SELECT ROWNUM SEQ, t.BUF
                FROM (
                        SELECT
                                'Data^B' || replace(REPLACE(UPPER(ttttt), '"'),'''') || '^B'
                                || replace(REPLACE(aaaaa, '"'),'''') || '^B'
                                || replace(REPLACE(bbbbb, '"'),'''')|| '^B'
                                || replace(REPLACE(ccccc, '"'),'''')|| '^B'
                                || replace(REPLACE(ddddd, '"'),'''')|| '^B'
                                || replace(REPLACE(eeeee, '"'),'''')|| '^B'
                                || USECOUNT
                                BUF
                        FROM NAME
                        WHERE
                                aaaaa LIKE '$a%'
                                AND bbbbb LIKE '$b%'
                                AND ccccc LIKE '$c%'
                                AND eeeee LIKE '$d%'
                                AND fffff = 'dd'
                                ORDER BY USECOUNT DESC
                ) t
                WHERE ROWNUM < $RLIMIT ) s
                WHERE s.SEQ >= $PSTART;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Even though this has been closed I wanted to throw out regular expressions:

regexp_replace(aaaaa,'"''','')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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