Find Character In All Fields

[andrewteg]

I'm looking for an easy way to find oddball characters and clean them up for a database. Basically we have a lot of pasted in data that includes data like â that should be a regular a and things of that nature.

I know it'd take a while but I'd like a way to identify all the occurrences of that in a table, a set of tables, or even an entire database if possible.

Is there a way to do that with MySQL or SQLyog or would I have to dump out the database data to a text file, search that, make a note of the table and row, and then go back into the live data at that end and fix it?

Thanks,

Andrew

[peterlaursen]

One thing you could try is to dump, 'search and replace' in an editor and import again.

However problems that may occur are:

1) dump may be too big for an editor (take one table at a time). I know that Ultraedit for instance handles very big files without being unacceptable slow.

2) if you have binary data (BLOBs) then

* such characters may occur in the textual display of such BLOB so here you should not 'search and replace' them of course.

* some editors may garble HEX patterns that are not valid UTF8.  With SQLyog Enterprise/Ultimate the 'scheduled backup' has an option to set the encoding of the dump. Make it latin1  (ANSI) for this special purpose – not utf8, as all HEX patterns are valid in ANSI.

* some editors may truncate long BLOB/TEXT/bulk inserts

— so be careful – do not drop the original tables before you are sure that it is OK!  For instance you could 'duplicate table', next export-edit-import the duplicate. Now rename the original and rename the duplicate to the original name.

[peterlaursen]

And besides I will move you to the SQLyog category!

[Author]

Posts