CODEWITHSUNDEEP
mysqlamazon-redshift
Patthebug
Patthebug

Reputation: 4797

Replacing single quote characters in a column in Amazon Redshift

I have some data in a table in the following format in Amazon Redshift:

Column1    Column2
'a'          'b'

I'd like to remove the single quote characters from this table and get the data as follows:

Column1    Column2
a            b

I would have thought the Replace function would do the trick, so I wrote the following query:

select replace(column1,''',''),
       replace(column2,''','')
from table

But this doesn't work and gives me Amazon](500310) Invalid operation: unterminated quoted string at or near "''',''). I tried to escape the single quote character by \ but even that didn't work.

I also tried using the following query:

select replace(column1,"'",''),
           replace(column2,"'",'')
    from table

But it gave me the error [Amazon](500310) Invalid operation: column "'" does not exist in <tablename>

So how do I remove these single characters from my data?

Any help would be much appreciated.

TIA.

Upvotes: 3

Views: 14319

Answers (2)

Rahul Gupta
Rahul Gupta

Reputation: 1802

select replace(column1,chr(39),''),
       replace(column2,chr(39),'')
from table

The CHR function returns the character that matches the ASCII code point value specified by of the input parameter.

If I’ve made a bad assumption please comment and I’ll refocus my answer.

Upvotes: 8

tadman
tadman

Reputation: 211740

With MySQL you have two ways of quoting, so you need to switch:

REPLACE(column1, "'", "")

You can't use the same character for both delimiting and content without escaping:

REPLACE(column1, '\'', '')

Upvotes: 5

Related Questions