• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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