Quotes about quotes

Occasionally I need to use quotes inside a string when dealing with a database. Question also turned up in the SQL and PL/SQL forum today.

Since tracking those quote marks is a pain here is a trick I've used in the past:

Write my statement using a caret (shift 6 or ^) in place of the quote around the literal, and using regular single quotes inside the literal. Of course, if a carat is supposed to be in the string, us another character - even a letter will do.


INSERT INTO ABC VALUES ( ^This is a 'quoted' string with several 'quote' marks.^, ^'quoted at start and end'^);

Then edit it to turn every single quote into two quotes so the literal handler will see those as quotes

INSERT INTO ABC VALUES ( ^This is a ''quoted'' string with several ''quote'' marks.^, ^''quoted at start and end''^);


And finally edit it to turn the caret into a single quote


INSERT INTO ABC VALUES ( 'This is a ''quoted'' string with several ''quote'' marks.', '''quoted at start and end''');

as in


C:\temp>sqlplus test/test

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 15 13:03:49 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> drop table abc;

Table dropped.

SQL> create table abc ( s1 varchar2(256), s2 varchar2(256));

Table created.

SQL> INSERT INTO ABC VALUES ( 'This is a ''quoted'' string with several ''quote'
' marks.', '''quoted at start and end''');

1 row created.

SQL> select * from abc;

S1
--------------------------------------------------------------------------------

S2
--------------------------------------------------------------------------------

This is a 'quoted' string with several 'quote' marks.
'quoted at start and end'




Of course, these days in Oracle I'd use the quote operator which uses the syntax

q'{delimiter}string{delimiter}'

as in


INSERT INTO ABC VALUES ( q'#This is a 'quoted' string with several 'quote' marks.#', q'!'quoted at start and end'!');

as in

SQL> delete from abc;

1 row deleted.

SQL>
SQL> INSERT INTO ABC VALUES ( q'#This is a 'quoted' string with several 'quote'
marks.#', q'!'quoted at start and end'!');

1 row created.

SQL> select * from abc;

S1
--------------------------------------------------------------------------------

S2
--------------------------------------------------------------------------------

This is a 'quoted' string with several 'quote' marks.
'quoted at start and end'