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.
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.
Try escaping the quotes:
'xxx''test''yy'
In SQL quotes can be escaped by adding another quote before them.
Use single quotes two times, instead of one double quotes. for eg:
INSERT INTO TIZ_VADF_TL_MODELS (name)
VALUES ('xxx''test''yy');
