CODEWITHSUNDEEP
oracle-databaseplsql
user595234
user595234

Reputation: 6259

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

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;
/

Upvotes: 163

Views: 441170

Answers (6)

Seyma Kalay
Seyma Kalay

Reputation: 2863

stmt := Q'[insert into MY_TBL (Col) values('ER0002')]';
stmt := Q'(insert into MY_TBL (Col) values('ER0002'))';
stmt := Q'{insert into MY_TBL (Col) values('ER0002')}';
stmt := Q'!insert into MY_TBL (Col) values('ER0002')!';
stmt := Q'<insert into MY_TBL (Col) values('ER0002')>';

Upvotes: 0

nhanhoangle
nhanhoangle

Reputation: 357

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

Upvotes: 2

cboler
cboler

Reputation: 739

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.

Upvotes: 38

Shravan Ramamurthy
Shravan Ramamurthy

Reputation: 4096

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.

Upvotes: 0

Corwin01
Corwin01

Reputation: 479

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.

Upvotes: 22

DCookie
DCookie

Reputation: 43533

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.

Upvotes: 259

Related Questions