How do I replace the ' (quotation) character using a MySQL statement

Question

I'm trying to create and XML feed with the result set of a SQL query. One of the fields contains ' characters which is causing an error in the resulting XML file.

How do I replace the ' character. I've read posts that suggest something like

Replace(my_column,'''','')

But this throws up a syntax error

Answer 1

Probably you're looking for something like this:

UPDATE your_table
   SET your_column = REPLACE(your_column, '\'', ''');

But then again, I wonder why it's necessary to do that in DB, and not in the output code.

Answer 2

It should work

select replace('abc''def','''','')

SQLFiddle example

Answer 3

Use ' in place of '. This will work:

Replace(my_column,"'",''')