PL/SQL, how to escape single quote in a string?
OraclePlsqlOracle Problem Overview
In the Oracle PL/SQL, how to escape single quote in a string ? I tried this way, it doesn't work.
declare
stmt varchar2(2000);
begin
for i in 1021 .. 6020
loop
stmt := 'insert into MY_TBL (Col) values(\'ER0002\')';
dbms_output.put_line(stmt);
execute immediate stmt;
commit;
end loop;
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
end;
/
Oracle Solutions
Solution 1 - Oracle
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.
Solution 2 - Oracle
Here's a blog post that should help with escaping ticks in strings.
Here's the simplest method from said post:
> The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides.
>
> SELECT 'test single quote''' from dual;
>
> The output of the above statement would be:
> test single quote'
>
> Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character.
>
> This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour.
Solution 3 - Oracle
In addition to DCookie's answer above, you can also use chr(39) for a single quote.
I find this particularly useful when I have to create a number of insert/update statements based on a large amount of existing data.
Here's a very quick example:
Lets say we have a very simple table, Customers, that has 2 columns, FirstName and LastName. We need to move the data into Customers2, so we need to generate a bunch of INSERT statements.
Select 'INSERT INTO Customers2 (FirstName, LastName) ' ||
'VALUES (' || chr(39) || FirstName || chr(39) ',' ||
chr(39) || LastName || chr(39) || ');' From Customers;
I've found this to be very useful when moving data from one environment to another, or when rebuilding an environment quickly.
Solution 4 - Oracle
EXECUTE IMMEDIATE 'insert into MY_TBL (Col) values(''ER0002'')'
; worked for me.
closing the varchar
/string
with two pairs of single quotes did the trick. Other option could be to use using
keyword, EXECUTE IMMEDIATE 'insert into MY_TBL (Col) values(:text_string)' using 'ER0002'
; Remember using
keyword will not work, if you are using EXECUTE IMMEDIATE
to execute DDL's with parameters, however, using quotes will work for DDL's.
Solution 5 - Oracle
In my case, I use like this:
stmt := q'!insert into MY_TBL (Col) values('ER0002')!';
Adding: q'! before and !' after string.
This is another reference: Alternative Quoting Mechanism (''Q'') for String Literals