CODEWITHSUNDEEP
sqlescapingamazon-redshiftsql-insertsingle-quotes
stack_pointer is EXTINCT
stack_pointer is EXTINCT

Reputation: 2393

Escaping single quotes in REDSHIFT SQL

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?

Upvotes: 12

Views: 52411

Answers (6)

tex6
tex6

Reputation: 1

double single quotes worked in my use case

Upvotes: 0

Anthony Kwiatkowski
Anthony Kwiatkowski

Reputation: 11

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

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

Upvotes: 1

Mario
Mario

Reputation: 11

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

Upvotes: 0

Swagatika
Swagatika

Reputation: 885

use \\' to escape '

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

Upvotes: -4

Greg Lewis
Greg Lewis

Reputation: 77

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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.

Upvotes: 22

Related Questions