How to Replace Single-Quote With Double-Quote in SQL Query - Oracle 10G

How to replace single-quote with double-quote in sql query - oracle 10g?

This should work:

UPDATE myTable
SET myField = REPLACE(myField, '''', '"');

Remove Single Quote from the column value in oracle sql

select replace (MSGID, '''', '') from schemaname_interface_daily_20110427;

Complete REPLACE function's documentation.

Remove single quotes in Oracle

Try REPLACE function:

replace(q'[(''acc','xyz'')]', q'['']',q'[']')

Demo: http://www.sqlfiddle.com/#!4/abb5d3/1

SELECT replace(q'[(''acc','xyz'')]', q'['']',q'[']')
FROM dual;

| REPLACE(Q'[(''ACC','XYZ'')]',Q'['']',Q'[']') |
|----------------------------------------------|
|                                ('acc','xyz') |

Oracle replace function on a string with single quotes and commas

Like this using the alternative quote operator: test := q'[a','b','c]';

Or:

SELECT REPLACE(q'[a','b','c]', 'a','b') 
FROM DUAL;

More info here.

Here's another way to prove it in Sqlplus:

SQL> declare
     test varchar2(15) := q'[a','b','c]';
   begin
   dbms_output.put_line(REPLACE(test, 'a', 'b'));

   end;
   /

b','b','c

PL/SQL procedure successfully completed.

SQL>

Replacing characters in a string when between double quotes

Short & clean.

with t(str) as (select 'randomtext,123,"JEAN SEBASTIEN, GUY, DANIEL",sun' from dual)
select regexp_replace(str,'(^[^"]*|[^"]*$)|,','\1') as result
from   t

-

+------------------------------------------------+
|                     RESULT                     |
+------------------------------------------------+
| randomtext,123,"JEAN SEBASTIEN GUY DANIEL",sun |
+------------------------------------------------+

SQL Fiddle

In addition -

Short and clean generic version

with t(str) as 
(
             select 'Well,you,went,uptown,riding,in,your,limousine' from dual
  union all  select 'With,your,fine,"Park, Avenue, clothes"' from dual
  union all  select 'You,had,the,"Dom, Perignon",in,your,hand,"And, the, spoon",up,your,nose' from dual
  union all  select '"And, when, you",wake,"up, in, the, morning"' from dual
  union all  select '"With, your, head, on, fire"' from dual
  union all  select '"And",your,"eyes, too, bloody","to, see",Go,"on, and, cry, in",your,coffee,"But","don''t","come, bitchin''","to, me"' from dual

)
select regexp_replace(str, '((^|").*?("|$))|,', '\1') as result
from   t 

--

+------------------------------------------------------------------------------------------------------------+
|                                                   RESULT                                                   |
+------------------------------------------------------------------------------------------------------------+
| Well,you,went,uptown,riding,in,your,limousine                                                              |
| With,your,fine,"Park Avenue clothes"                                                                       |
| You,had,the,"Dom Perignon",in,your,hand,"And the spoon",up,your,nose                                       |
| "And when you",wake,"up in the morning"                                                                    |
| "With your head on fire"                                                                                   |
| "And",your,"eyes too bloody","to see",Go,"on and cry in",your,coffee,"But","don't","come bitchin'","to me" |
+------------------------------------------------------------------------------------------------------------+

SQL Fiddle

PL/SQL, how to escape single quote in a string?

You can use literal quoting:

stmt := q'[insert into MY_TBL (Col) values('ER0002')]';

Documentation for literals can be found here.

Alternatively, you can use two quotes to denote a single quote:

stmt := 'insert into MY_TBL (Col) values(''ER0002'')';

The literal quoting mechanism with the Q syntax is more flexible and readable, IMO.

---

Related Topics