Tag Archives: Query

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

Oracle query to find the server start up time

One of my colleagues showed me this gem in Oracle and I thought of documenting it here so that it will be useful for everyone and also I can find it next time I need it by just searching my blog instead of Google. 🙂 Using this query you can find out the start time of the Oracle database server.

SELECT * FROM V$INSTANCE

In addition to the start time the query also returns other useful information like instance name, version, hostname etc. The hostname is particularly useful when TNSNAMES.ORA is playing tricks with you.

Posted in Database Programming | Tagged , | 3 Comments