Loop through MySQL database changing apostrophes with UTF-8 version

Question

I've been having some trouble with my website + database, the content was added to the database using a java app using some unknown charset and they are being displayed in PHP/HTML using UTF-8 <meta charset='UTF-8'> but im getting a bunch of diamond shaped question marks.

I've tried changing the charsets around and using the htmlentities function but i'm not having much luck. I was wondering if its possible to loop through a database table removing all instances of some character (old apostrophes) and replacing it with the utf-8 (apostrophes) version?

I've also set the database charset to utf-8. It's just this old content i'm having trouble with.

Old apostrophe: ’

Desired apostrophe: '

@ElefantPhace as you can see this stuff is making me lose my mind too :p — Crizly, Jul 21 '15 at 00:46

score 2 · Accepted Answer · answered Jul 21 '15 at 00:46

2

You would need to write a query for each table, like this:

UPDATE table1
SET field1 = replace(field1, "’", "'"),
field2 = replace(field2, "’", "'")
etc.

answered Jul 21 '15 at 00:46

Ben Fried

2,168
1
14
13

Sounds good. I couldn't test it, so you might need to escape the single quote differently than I posted above. For example it might be replace(field1, '’', '''') or replace(field1, '’', '\'') if the double quotes don't work. – Ben Fried Jul 21 '15 at 00:51

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

The old content was added to the database using a non UTF8 charset and thats why its showing up as �. Unfortunately a thorough fix wont be possible as all symbols don't transliterate well to UTF8. You might want to simply Update the ones that you find like the apostropes ’ . You might also find useful information here

Edit: The update i mentioned would be like Ben's query

edited May 23 '17 at 12:29

Community

1
1

answered Jul 21 '15 at 00:50

Shreyas Chavan

1,079
1
7
17

Loop through MySQL database changing apostrophes with UTF-8 version

2 Answers2