filter out characters in mysql results and save to file

we have a database of over 400000 products and are exporting them to google base.  The thing is in certain products descriptions somehow there are charcacters such as ’ inplace of a ' and characters – inplace of |, etc which google doesn't like.

i used mysql REPLACE on these to switch them to their respective characters, but when i look at the output file they still show up there.  This is when i run it from the shell or save the results to a file with >.

if i run a query with the mysql function i made to clean these in phpmyadmin everything looks fine.

any ideas here.  by the way we didn't use a regular mysql connector like in php or something to run the query and go through the results because the results are very big and because of result caching this took a few hours to complete instead of a query in few seconds. if you run mysql from the shell with --quick this keeps from caching the results

Any ideas on how to get rid of these characters?

also we can't use outfile because the db server is a separate machine
hillelbenAsked:
Who is Participating?
 
mankowitzCommented:
I'd duplicate the table and then change the collation. You can always remove the duplicate table later, but you can't restore characters that should be there.
0
 
mankowitzCommented:
One option is to run the output through another filter to fix the charactes

%mysql -Uxxxx --xxx ..... | perl -nfe 's/[^[:print:]]/_/g;' > output.txt

You could also make a duplicate of the table and change the collation to ascii_general_ci which should remove those characters.
0
 
flytox06Commented:
You certainly stored chars of a different charset from the database one.

ex: you stored utf8 from website into a utf8 database but through a latin1 connection.

you should ensure that the path of data is using similar charset from client to database/table.
once you understood what you stored and where, you should be able to correctly extract the informations.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hillelbenAuthor Commented:
is there a way to change the collation of query results?
0
 
hillelbenAuthor Commented:
that is change the character set to the one the system is using
0
 
flytox06Commented:
you can change live many parameters including collation by using SET NAMES .

SET NAMES 'charset_name' [COLLATE 'collation_name']

more info on http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
0
 
hillelbenAuthor Commented:
or actually would there be any problems with changing the collation of the table i'm using?
0
 
hillelbenAuthor Commented:
thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.