I recently bought a database with 170.000 entries, all of which had some stupid link at the end of one of their fields. Let’s say it was a cocktail recipes database and the links in the end were some advertisements like “if you want more recipes visit blahblah”.

I of course didn’t want these links to appear in my posts since this is my database now and I don’t like lame links in it. What to do, to remove each of these links, without the need to manually edit every single line?

It was actually a very easy task after some researching. I found out that mysql has the same function as php’s, str_replace function.

In php, “Hello_world_!” with this function

echo str_replace('_', '-', 'Hello_world_!');

Becomes “Hello-world-!”. In mysql, to get this result, we use it’s replace() function. So to reproduce the effect of that php function we would write something like:

[mysql]
update content set recipe = replace(recipe, ‘_’, ‘-’);
[/mysql]

So if we want to delete those stupid links from our recipe fields, we would have to use:

[mysql]
update content set recipe = replace(recipe, ‘stupid link’, ”);
[/mysql]

And this would change all our records, deleting the “stupid link” where it is present. It is a useful function when we need to change, giving another example, all of our old tags to the new ones (<b> to <strong> or i to em etc..).