Insert string with apostrophe(') in sql

when using Insert statement to inset string type fields and the values has an apostrophe('), how

the inset statements can be handled in such cases.


SQL> insert into donuts values('jelly's,9);
insert into donuts values('jelly's,9)
                                   *
ERROR at line 1:
ORA-00917: missing comma


SQL> insert into donuts values('jelly''s,9);
ERROR:
ORA-01756: quoted string not properly terminated

The Error displayed here is due to string data types that are delimited by the single quotation mark and the apostrophe(') is considered as the end of the string  delimiter.

The possible way is to escape single apostrophe (') with an extra apostrophe as below.


SQL> insert into donuts values('jelly''s',9);

1 row created.

Now the string with apostrophe is inserted into the table.

SQL> select * from donuts;

NAME	PRICE
----------	----------
jelly	9
jell's	9