Stripping characters permanently

[Anon]

I recently converted a Lotus Notes database to mySQL by:

1. Exporting from Notes to Lotus 1-2-3
2. Converting 1-2-3 to Comma Delimited via Excel
3. Importing the CSV file into the new database.

For some reason, All of the imported fields have a '\0' after them (which you can't actually "see", but you must include if you're doing a search) which isn't a problem except that any NEW data added to the database DOESN'T have the \0, therefore, the accuracy of any queries is now in question.

How can I permanently remove the \0?

Many thanks in advance!

[Anon]

Try this:

update <TABLE_NAME> set <COLUMN_NAME>=replace(<COLUMN_NAME>, '\0', '');

I haven't tested it yet, though. Just don't forget to espace the backslash (\0) if you're running the query from PHP.

HTH
Dario Gomes