insert string which includes quotes in oracle

sql string oracle sql-insert clob
neverwinter picture neverwinter · Feb 16, 2014 · Viewed 25.8k times · Source

How can I insert string which includes quotes in oracle? my code is

INSERT INTO TIZ_VADF_TL_MODELS (name)
VALUES ('xxx'test'yy');

if I use 

INSERT INTO TIZ_VADF_TL_MODELS (name)
VALUES ("xxx'test'yy");

I get identifier is too long error because xxx'test'yy is clob.

how can I do that?

thx.

Answer

Alex Poole picture Alex Poole · Feb 16, 2014

You can also use the 'alternative quoting mechanism' syntax:

INSERT INTO TIZ_VADF_TL_MODELS (name)
VALUES (q'[xxx'test'yy]');

The pair of characters immediately inside the first set of quotes, [] in this case, delimit the quoted text; single quotes within those do not have to be escaped. Of course, you can't then have ]' within the string itself, but you can pick your own delimiters so that can be avoided if it's going to be an issue; ] on its own would still be OK.

This can be simpler than making sure single quotes are escaped, which can get a bit messy, or at least hard to read and debug.

SQL Fiddle.

Related questions

Splitting string into multiple rows in Oracle

I know this has been answered to some degree with PHP and MYSQL, but I was wondering if someone could teach me the simplest approach to splitting a string (comma delimited) into multiple rows in Oracle 10g (preferably) and 11g. …

Read More
Cleanest way to build an SQL string in Java

I want to build an SQL string to do database manipulation (updates, deletes, inserts, selects, that sort of thing) - instead of the awful string concat method using millions of "+"'s and quotes which is unreadable at best - there …

Read More
Why does Oracle 9i treat an empty string as NULL?

I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is …

Read More