Oracle replace function on a string with single quotes and commas

Question

I have a query in the form

SELECT REPLACE(text, 'a','b') 
FROM DUAL;

if the string text = a','b','c

How do I place the string in the replacd function so that it runs correctly?

Answer 1

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>

Answer 2

Escape each single quote with a second single quote:

SELECT REPLACE( 'a'',''b'',''c','a','b') FROM DUAL