Replacing Characters in a table column

Hey. I've recently noticed that some of the letters didn't quite translate well into the database when imported.

Basically characters like –’ came out really silly.

Does anyone know of a sql call to filter these out and replace them with normal versions (-')?

Thanks.

 

characters like this?

â€Â

 

Currently I'm having major problems with the following four:

–
’
“
”

The database entries for these chars result in a vertical rectangle and a space.

 

i had character problems when i moved a forum database. the message table was loaded up with different strings similiar to this: … they replaced the punctuation code.

! got changed to …â€Â

, got changed to …

etc, was weird

so i identified which string represented which punctuation mark and replaced them. i had to export that table and manually make the changes, around 30,000 of em, took an hour or so.

 

Manual replacements, huh? Hehe.

Isn't there any automated way to do this?

 

Well, you can use the replace() function:

update my_table set my_field=replace(my_field, 'old_text', 'new_text');

You'll have to repeat that for each bad character, but since there's only four, it's doable (run the query four times).

 

I use this function all the time.. It's the best "find and replace" function that I can find too.