Inserting string with special characters is a pain in the butt, it would seem. It’s easy to escape a string with single quote or ampersand in SQL statements.

To escape a single quote, use two single quotes instead of one. Too easy, right?

INSERT INTO CONFIGURATION VALUES (156456,'USE PH''S INSTEAD')

Unfortunately, this trick wouldn’t work with other special characters. This following statement would throw error.

//Error - 
INSERT INTO ASSETS (asset_name) values ('Phil & Jones Assets');

By default, SQL Plus treats ‘&’ as a special character that begins a substitution string. This can cause problems when running scripts that happen to include ‘&’. Similarly Oracle SQL developer treats & the same way. You can use the following tricks to escape special characters.

SQL Server:

If you know your SQL statement includes (or may include) data containing ‘&’ characters, and you do not want the substitution behaviour as above, then use SET QUOTED_IDENTIFIER OFF; to switch off the behaviour while running the script.

SET QUOTED_IDENTIFIER OFF;

INSERT INTO ASSETS (asset_name) values ('Phil & Jones Assets');

SET QUOTED_IDENTIFIER ON;

You might want to add SET QUOTED_IDENTIFIER ON at the end of the script to restore the default behaviour.

Oracle:

SET DEFINE OFF;

INSERT INTO ASSETS (asset_name) values ('Phil & Jones Assets');

SET DEFINE ON;