Replacing text strings MySQL

Question

I've recently moved to a different platform for my personal website, and I've run into a problem where the previous encoding of characters such as ", "", and ' are now recoded strangely as:

“
”
’
’
'

I've seen this before, and last time, I went through manually and updated each article. This time, however, I'd like to take a more pragmatic approach by updating the database.

How would I go about replacing all occurences of these strings with their correct character?

I'm thinking that it would be somehting like:

SELECT REPLACE(''',''')

But do I need to be cautious and include escape characters like \? Also, how would I perform this type of replacement across the entire database?

Note: I'll be using phpMyAdmin to perform these replacements, so I'm hoping that it's just a matter of typing a series of commands into the "SQL" tab. Although, I do have access to the MySQL server from the command line if it's necessary.

Update:

More about the structure:

• The table name is "field_data_comment_body"
• The field name is "comment_body_value"
• The field in question is of type "longtext"

I've tried running Johan's recommendation, but it returns 0 Affected rows:

DELIMITER $$

CREATE FUNCTION FixEncoding(input longtext) RETURNS longtext
BEGIN
  DECLARE output longtext;

  SET output = input;   
  SET output = REPLACE(output,''','\'');
  SET output = REPLACE(output,'’','\'');
  SET output = REPLACE(output,'” ','"');
  SET output = REPLACE(output,'“','"');
  SET output = REPLACE(output,'’','\'');

  RETURN output;
END $$

DELIMITER ;

UPDATE field_data_comment_body SET comment_body_value = FixEncoding(comment_body_value) WHERE entity_id <> 0;

Update: It's not a translation error as this returns 63 rows:

SELECT  `comment_body_value` 
FROM  `field_data_comment_body` 
WHERE  `comment_body_value` LIKE  '%'%'
LIMIT 0 , 30

Answer 1

In MySQL characters can be escaped by using \.

I'd write a function to do the replacing for you and than do an update, something like this.

DELIMITER $$

CREATE FUNCTION FixEncoding(input varchar) RETURNS varchar
BEGIN
  DECLARE output varchar;

  SET output = input;   
  SET output = REPLACE(output,''','\'');
  SET output = REPLACE(output, .....
  .....

  RETURN output;
END $$

DELIMITER ;

UPDATE table1 SET column1 = FixEncoding(Column1) WHERE id <> 0;

If this doesn't work then you might be suffering from translation issue between the database and the presentation layer.
Make a backup of your database
and change the encoding of your table(s) by using:

ALTER TABLE `test`.`test` CHARACTER SET latin1 COLLATE latin1_general_ci;