Tag Archives: replace

Find and replace strings in MySQL

Recently, I was checking the apache error logs of a project I was working on and found that it has lot of 404 errors for images. I used my apache error parsing script and found that most of the errors were because the production db was still having some references to images present in the stage machine.

I had to do a quick find and replace in the MySQL db and after a quick search came up with the following query, using the replace function.

UPDATE table SET field = REPLACE(field, 'stage-url', 'prod-url');

Since I was about to run this query on a prod db directly, I wanted to add another condition to replace only rows that contained the stage url as an additional precaution (of course after taking backup of the db 😉 ).

UPDATE table SET field = REPLACE(field, 'stage-url', 'prod-url') WHERE INSTR(field, 'stage-url') > 0;

Hope this query is useful for someone who also had a shock like me when they looked at the apache error logs 😉

Posted in Database Programming | Tagged , , , | 3 Comments