Mysql syntax for substring search and replace

[Ataraxia]

I have a mysql-based links database that has many "deep" links to different pages within the same site. Let's call the fieldname for this "URLS" and tablename to be "LINKS"

Now and then the base URL of these links change but not the page names.

For example:

[ZZZZ.com...]

might change to:

[YYYYYYYYYYYY.com...]

The URLs to all other pages at this particular site would similarly change. Note that the change involves more characters.

QUESTION: what should the wording of a MySQL query be to change all occurences of the substring "ZZZZ.com/zzz" in the URLS field in the LINKS table to "YYYYYYYYYYYY.com/yyyyyyy"

(I'm very new to this, obviously. Thanks for your help!)

[ergophobe]

Surprisingly easy

table junk
id ¦ f1
1 ¦ asdf John asdf
2 ¦ Johnasdf
3 ¦ asdf asdf asdfJohn

UPDATE junk SET f1=(REPLACE (f1, 'John','Ringo'));

table junk
id ¦ f1
1 ¦ asdf Ringo asdf
2 ¦ Ringoasdf
3 ¦ asdf asdf asdfRingo

[Ataraxia]

Thanks-

However, that appears to be how to change only the record with ID# f1. (Or am I mistaken in this assumption?)

How could I do a mass search/replace for any occurence of the ZZZ strings in all records?

Thanks again....

[ergophobe]

No. It changes all occurrences in the field f1.

If you want to restrict it to particular rows, you'll need to add a WHERE clause.

[coopster]

Welcome to WebmasterWorld, Ataraxia!

The MySQL manual comes in quite handy. The String Functions [dev.mysql.com] page is where you can see what each argument represents in the example ergophobe gave here.

[ergophobe]

Yes, welcome! Sorry, I didn't notice the post count.

BTW, the manual is, obviously, a great resource, but unfortunately most examples on the string functions page (including replace) just illustrate with SELECT and it's sometimes not obvious how to make the jump from the example to the task you're working on (at least for me).

I find the MySQL manual so much less friendly than the PHP manual, whether it's finding something you've seen before, finding something new, or just trying to figure something out, it often strikes me as unfriendly.

Tom

[Ataraxia]

I tried Ergophobes suggested command and it worked perfectly!

Thanks again, Ergo!