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?

score 21 · Answer 1 · answered Dec 28 '17 at 00:54

21

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.

answered Dec 28 '17 at 00:54

Gordon Linoff

1,242,037
58
646
786

2

The table only has 2 columns and both are VAR CHAR. I've tried with double single quotes as well. It's not working. – stack_pointer is EXTINCT Dec 28 '17 at 00:58
@stack_pointerisEXTINCT . . . This is quite strange. Are you sure you are using the "simple" single quote and not some fancier character? – Gordon Linoff Dec 28 '17 at 01:57
I noticed a different problem was there because of commas used within the names and it was a CSV file. – stack_pointer is EXTINCT Dec 28 '17 at 23:57

score 1 · Answer 2 · answered Apr 16 '20 at 01:45

1

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

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

answered Apr 16 '20 at 01:45

Anthony Kwiatkowski

11
1

score 0 · Answer 3 · edited Feb 17 '19 at 10:45

0

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.

edited Feb 17 '19 at 10:45

Zoe

27,060
21
118
148

answered Feb 15 '19 at 19:27

Greg Lewis

67
1
1
8

Mario · Answer 4 · 2019-05-01T19:39:16.257

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

score 0 · Answer 5 · answered Jul 18 '23 at 14:45

0

double single quotes worked in my use case

answered Jul 18 '23 at 14:45

tex6

1

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](/help/how-to-answer). – Community Jul 21 '23 at 12:11

score -3 · Answer 6 · edited Feb 17 '19 at 10:45

-3

use \\' to escape '

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

edited Feb 17 '19 at 10:45

Zoe

27,060
21
118
148

answered Apr 04 '18 at 22:30

Swagatika

857
1
11
32

3

This is not something you can do in Redshift. – szeitlin Jan 30 '19 at 22:51

Escaping single quotes in REDSHIFT SQL

6 Answers6