Escaping single quotes in REDSHIFT SQL

Question

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?

Bloodshed

Answer 1

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.

Telly

The table only has 2 columns and both are VAR CHAR. I've tried with double single quotes as well. It's not working. – Bloodshed

@stack_pointerisEXTINCT . . . This is quite strange. Are you sure you are using the "simple" single quote and not some fancier character? – Telly

I noticed a different problem was there because of commas used within the names and it was a CSV file. – Bloodshed

Answer 2

I think it may depend on your environment. I'm using Periscope Data's redshift SQL editor, and \ worked as an escape character. '' and \\ did not work.

Chasseur

Answer 3

You could use CHR(39) and concat the strings. Your name would look like below:

  ('O' || CHR(39)||'Brian')

Hootman

Answer 4

I was facing similar problem , I was needing send a kind of JSON structure to then decode it into my query but there was a program receiving my string and this program was escaping my escapes, so the query fails, finally I found this : Put $$ in dollar-quoted string in PostgreSQL mentioning quote_literal(42.5) https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-OTHER This resolves my issue . an example String is
'LocalTime={US/Central}; NumDays={1}; NumRows={3}; F_ID={[Apple, Orange, Bannana]}'

Select  
   Param, value , replace(quote_literal(replace(replace(Value,'[',''),']','')),',',quote_literal(',')) ValueList 
    FROM (
    select 
        SPLIT_PART(split,'=',1) as Param,
        replace( replace(SPLIT_PART(split,'=',2),'{',''),'}','') as Value
    FROM 
    (
        select 
            trim(split_part(freeform.txt, ';', number.n)) as split 
        from 
        (   select 
            'LocalTime={US/Central}; NumDays={1}; NumRows={3}; F_ID={[Apple, Orange, Bannana]}' as txt
        ) freeform,
        (   select 1 as n union all
            select 2 union all
            select 3 union all
            select 4 union all
            select 5 union all
            select 6 union all
            select 7 union all
            select 8 union all
            select 9 union all
            select 10
        ) number
        where split <> ''
    ) as MY_TMP
    ) as valuePart

Undercroft

Answer 5

double single quotes worked in my use case

Herron

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Rozek

Answer 6

use \\' to escape '

s = s.replace("'", "\\'")

Polson

This is not something you can do in Redshift. – Bessette