• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Chrispian H. Burks

coder. maker. writer. photographer.

  • Home
  • Dreams
  • Dev Links
  • About Me
  • Contact

MySQL Search and Replace

Published on: January 16, 2008

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]

Filed Under: Uncategorized

About Chrispian

My name is Chrispian and I like to make things. I'm a web developer and have been working in the computer / internet field for almost 26 years now. I also draw, write, make things, take pictures and love figuring stuff out. Read More

Reader Interactions

Comments

  1. ByeVas says

    November 13, 2008 at 11:40 am

    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. Kapoue says

    April 21, 2011 at 1:16 pm

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

  3. Chrispian says

    April 21, 2011 at 1:36 pm

    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.

  4. Dirk says

    September 12, 2019 at 2:21 am

    Thats exactly what I’ve been looking for.
    Our affiliate system is changing their complete link structure on October 1st.
    Envato is switching completely to Impact Radius.

    We do have about 18.000 Links like
    https://themeforest.net/item/our-magazine-magazine-wordpress-Layout/full_screen_preview/32807?ref=affiliateID

    Insted of deleting those posts in our WordPress Site, we would like to change those OLD URLs to the new structure.

    We want to change URLs like
    https://themeforest.net/item/our-magazine-magazine-wordpress-Layout/full_screen_preview/32807?ref=affiliateID

    to

    https://1.envato.market/yrYQB?u=https://themeforest.net/item/our-magazine-magazine-wordpress-theme/32807

    So we “just” have to get rid of the URLs ending “?ref=affiliateID” and will have to add the “https://1.envato.market/yrYQB?u=” at the start of the URL.

    Any idea of how to resolve this issue?

  5. Chrispian says

    September 12, 2019 at 4:00 pm

    Exactly. You might need to do more than one pass on it, like first update it to remove the /?ref=affiliateID (based on your example, the slash needs to be removed too) part and then do one to prepend the new url bit on the front.

    If you need help writing the queries, I do a little consulting on the side so feel free to get in touch.

  6. Tyler C says

    October 27, 2021 at 8:38 am

    Thanks for the post. A question I have regarding this topic: the fields that I need to do this on will change a number 777998726 to 000000000. The field contains a bunch of other information that I do not want to replace. I only want to touch the 777998726 and leave the rest of the information untouched. The table has several records containing serialized information. Below is an example of one of the records.

    Example:
    Field name: “has_been_approved_by”
    data in the field: “i:0;s:9:”777998726″;i:1;s:9:”777999456″;i:2;s:9:”777998420″;i:3;s:9:”777999634″;i:4;s:9:”777999048″;i:5;s:9:”777997919″;i:6;s:9:”777998295″;i:7;s:9:”777997987″;i:8;s:9:”777998717″;i:9;s:9:”777386584″;i:10;s:9:”777999563″;i:11;s:9:”777998924″;i:12;s:9:”777999743″;}”

    Would this method work and preserve the other serialized data?

Leave a Reply

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

Primary Sidebar

My name is Chrispian and I like to make things. I'm a web developer and have been working in the computer / internet field for almost 26 years now. I also draw, write, make things, take pictures and love figuring stuff out. Read More

Let’s Connect

  • Facebook
  • GitHub
  • Instagram
  • LinkedIn
  • Twitter

Copyright © 2023 · Groovy 1976 Theme by Chrispian H. Burks · Built using Genesis Framework & WordPress Log in