Inserting string with special characters into a column

Question

How can I successfully run such a query in SqlDeveloper?

execute immediate q'#
                    insert into results(SrcProcedure) 
                                values('d/&'s*73;.f45')
                    #';

possible duplicate of [Escaping single quote in PLSQL](http://stackoverflow.com/questions/6678478/escaping-single-quote-in-plsql) — Politank-Z, Apr 28 '15 at 13:08
Possible duplicate of [Which "special" characters are allowed in SQL Server varchar fields?](http://stackoverflow.com/questions/7038213/which-special-characters-are-allowed-in-sql-server-varchar-fields) — Ashraf Sada, Jun 16 '16 at 19:54

score 3 · Answer 1 · edited May 23 '17 at 12:31

You need to learn the escaping rules for your language. In SQL, ' is a special character. The escape for it is '':

execute immediate q'#
                insert into results(SrcProcedure) 
                            values(''d/&''''s*73;.f45'')
                #';

Rule: For each level of nesting, you need to duplicate all single quotes.

d/&'s*73;.f45
'd/&''s*73;.f45'
...values(''d/&''''s*73;.f45'')...

You can get away with less quotes if you use q'{...}':

execute immediate q'{
                insert into results(SrcProcedure) 
                            values('d/&''s*73;.f45')
                }';

The alternative quoting syntax works with almost any character as the quote_delimiter. `q'# ... #';` works fine. The problem is that the string inside the string, inside the string must be escaped. — Jon Heller, Feb 29 '16 at 03:36

score 1 · Answer 2 · edited Feb 29 '16 at 03:38

The problem is that you are trying to insert the value d/&'s*73;.f45 and the ' character is interpreted as the end of the value.

You need to escape the ' character:

execute immediate q'#
                insert into results(SrcProcedure) 
                            values('d/&''s*73;.f45')
                #';

Or you can nest the alternative quoting mechanism to avoid any string escaping:

execute immediate q'#
                insert into results(SrcProcedure) 
                            values(q'!d/&'s*73;.f45!')
                #';

Inserting string with special characters into a column

2 Answers2