big replace in mysql table

ChicoMendez

any one know how i replace ’ with ' in a big mysql database table ?

tnx

Adam

Do you want to run through the database and replaces it in the content, or just replace the characters as they are returned from a query?

ChicoMendez

HI
id like to run through the db if possible.
its just one field on teh table im interested in, in fact

tnx

louie

One way is by doing a query select from the table where field like "'’%'" then loop through records and use the str_replace("’","'",field) or preg_replace functions, then update database with the new value.

Depending on the amount of records you have it might take a while.

seamus

MySQL has a replace function

UPDATE 
    myTable 
SET
    myVarCharCol = REPLACE(myVarCharCol, '’', '\'')

Details here:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

It may be worth your while building a fulltext index on the column in question to speed it up, but assuming it's a once-off replace, that would be overkill.

Backup your table/database first!

ChicoMendez

Perfect seamus - that worked like a charm! TNX