Removing strange characters from MySQL data

Question

Somewhere along the way, between all the imports and exports I have done, a lot of the text on a blog I run is full of weird accented A characters. When I export the data using mysqldump and load it into a text editor with the intention of using search-and-replace to clear out the bad characters, searching just matches every "a" character. Does anyone know any way I can successfully hunt down these characters and get rid of them, either directly in MySQL or by using mysqldump and then reimporting the content?

Accepted Answer

This is an encoding problem; the `Â` is a non-breaking space (HTML entity ` `) in Unicode being displayed in Latin1.

You might try something like this... first we check to make sure the matching is working:

```
SELECT * FROM some_table WHERE some_field LIKE BINARY '%Â%'
```

This should return any rows in `some_table` where `some_field` has a bad character. Assuming that works properly and you find the rows you're looking for, try this:

```
UPDATE some_table SET some_field = REPLACE( some_field, BINARY 'Â', '' )
```

And that should remove those characters (based on the page you linked, you don't really want an nbsp there as you would end up with three spaces in a row between sentences etc, you should only have one).

If it doesn't work then you'll need to look at the encoding and collation being used.

EDIT: Just added `BINARY` to the strings; this should hopefully make it work regardless of encoding.

Removing strange characters from MySQL data

Solution

Problem