MySQL Search and Replace

I had to update a link for a client today and most of their content is database driven. This makes it easy to do quick searches and even search and replace. Most databases support this and the syntax is pretty close, but this tip is specifically for MySQL.

So, lets say you have an affiliate link that looks like this:

http://www.domain.com/aff.php?affid=1234

But you get an email from your affiliate manager telling you to update your links to a new format like this:

http://affiliates.domain.com/offer/1234

MySQL will make that easy.

First, lets check to see how many of our records have that url:

SELECT * FROM table_name WHERE field_name like ‘%http://www.domain.com/aff.php?affid=1234%’

In plain English, that means, select everything from the table named ‘table_name’ where the filed named ‘field_name’ is like the url. The % signs around the url are “wildcards”.

Lets say it returned 189 records. Now lets do our replace

UPDATE table_name SET field_name = REPLACE(field_name, “http://www.domain.com/aff.php?affid=”, “http://affiliates.domain.com/offer/”) WHERE field_name like ‘%http://www.domain.com/aff.php?affid=1234%’

This query is a little more complex, but still pretty straight forward. You should see 189 records updated. You don’t have to use the where clause in this query, but I do simply because it speeds things up and it’s a good habit to always use a where clause unless you really need for your query to look at or check every record. In that query, inside the replace, the first field is the OLD string that you want to replace and the second one is the NEW string you want to replace it with. Just like search and replace using your favorite text editor.

[tags]MySQL, Search, Replace[/tags]

Comment section

3 thoughts on “MySQL Search and Replace

  1. The best example over the net. I have been (re)searching this topic for quite some time unfortunately there is a bunch of logs quoting this “update [table_name] set [field_name] = replace([field_name],'[string_to_find]’,'[string_to_replace]’);”

    which works only in the most idiotic cases. The examples above are golden :)

    Thanx!

  2. Thank you very much for this exemple. Like ByeVas said, everyone is quoting the same textbook exemple.

  3. My pleasure. This was a tough subject when I was learning and the examples, as pointed out, have always been weak. I’ll do an updated tutorial and include some more examples and tricks.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.