CODEWITHSUNDEEP
sqlstringoracle-databasesql-insertclob
neverwinter
neverwinter

Reputation: 810

insert string which includes quotes in oracle

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.

Upvotes: 13

Views: 45055

Answers (3)

Rashmi singh
Rashmi singh

Reputation: 155

Use single quotes two times, instead of one double quotes. for eg: 

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

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 19

Óscar López
Óscar López

Reputation: 236004

Try escaping the quotes:

'xxx''test''yy'

In SQL quotes can be escaped by adding another quote before them.

Upvotes: 13

Related Questions