I’m forever doing web searches for little snippets I know I should have kept a note of somewhere, and I’ve decided to add them to the blog from now on as if I find them useful, someone else might too.

The MySQL replace function is one such thing that I use every so often to save myself time:

REPLACE(str,from_str,to_str)

It is great for replacing text strings in a table, saving you time. For example, I have a table called “security_pages” where a directory has been renamed and all entries in the page field (there are about 120 of them) need to be edited from “/olddirectory/page.php” to “/newdirectory/page.php”.

This would be tedious by hand, but the following code will do it quickly (Query took 0.0007 sec):

UPDATE security_pages
SET page =  REPLACE ( page, "/olddirectory/", "/newdirectory/" )
WHERE 1=1

Just replace the variables accordingly. If, like me, you have learnt that playing it fast and loose means the occasional “doh!”, you may want to grab a backup of the table before running a command like this.

ADVERT: If you are scratching your head, I am a certified MySQL professional and available for hire. Email talk@cuplaweb.com.