Escaping single quotes in REDSHIFT SQL

sql escaping amazon-redshift sql-insert single-quotes
stack_pointer is EXTINCT picture stack_pointer is EXTINCT · Dec 28, 2017 · Viewed 23.2k times · Source

I've lots of string values containing single quotes which I need to insert to a column in REDSHIFT table. I used both /' and '' to escape the single quote in INSERT statement.

e.g. 

INSERT INTO table_Temp
    VALUES ('1234', 'O\'Niel'), ('3456', 'O\'Brien')

I also used '' instead of \' but it keeps giving me error that "VALUES list must of same length" i.e. no: of arguments for each record >2.

Can you let know how to have this issue resolved?

Answer

Gordon Linoff picture Gordon Linoff · Dec 28, 2017

The standard in SQL is double single quotes:

INSERT INTO table_Temp (col1, col2)  -- include the column names
    VALUES ('1234', 'O''Niel'), ('3456', 'O''Brien');

You should also include the column names corresponding to the values being inserted. That is probably the cause of your second error.

Related questions

Escaping ampersand character in SQL string

I am trying to query a certain row by name in my sql database and it has an ampersand. I tried to set an escape character and then escape the ampersand, but for some reason this isn't working and I'm …

Read More
Oracle SQL escape character (for a '&')

While attempting to execute SQL insert statements using Oracle SQL Developer I keep generating an "Enter substitution value" prompt: insert into agregadores_agregadores ( idagregador, nombre, url ) values ( 2, 'Netvibes', 'http://www.netvibes.com/subscribe.php?type=rss\&url=' ); …

Read More
Java - escape string to prevent SQL injection

I'm trying to put some anti sql injection in place in java and am finding it very difficult to work with the the "replaceAll" string function. Ultimately I need a function that will convert any existing \ to \\, any " to \", any …

Read More