Find and Replace in database

[MetalRush]

First: a great and healthy 2010 for all you, out there

Second: my question seems fairly easy to some of you. Please bare with me, since I am not an expert on MySQL or any other db for that matter...

Situation: I created a new website and started out with phpBB3 (out of the box).
After a few small mods and changes I decided to move the forum to a sub-domain (from http://www.BlaBla.com to http://forum.BlaBla.com). This is working great.

Problem: since the move, there are several links in users postings, where they linked back to another thread (ergo: http://www.BlaBla.com/Link...). I want to change this, with a SQL statement, to http://forum.BlaBla.com/Link...
I do not know which tables to touch and how to create a decent statement.
Is there anyone who can help me out, please? I did try via all kind of Google links, but I did not succeed (my tries ended all in an error (wrong statement)).

Please keep in mind that not only a link is directly in several postings, but also embedded in their text (used the code).

TIA!

[HGN]

And maybe within [img] code.

First make a back-up of your database (or at least the table phpbb_posts).
Then run the queries:

Code: Select all

update phpbb_posts set post_text = replace(post_text, 'www.BlaBla.com/Link', 'forum.BlaBla.com/Link');
update phpbb_posts set post_text = replace(post_text, 'www.BlaBla.com/Link', 'forum.BlaBla.com/Link');

Prefix phpbb_ may be different in your database; replace 'BlaBla' and 'com/Link' to what is applicable for you.

[MetalRush]

Hi Alfred,

Bedankt voor je snelle reactie
(translated: thanks for the quick reply).

If I would change your query to:

Code: Select all

    update phpbb_posts set post_text = replace(post_text, 'www.BlaBla.', 'forum.BlaBla.');
    update phpbb_posts set post_text = replace(post_text, 'www.BlaBla', 'forum.BlaBla');

...in order to change ALL links where www.BlaBla is changed to forum.BlaBla
So I left out the ".com/Link...'
Would that work?

[HGN]

Yes, that should work. You should be absolutely sure you want all www.BlaBla changed in forum.BlaBla though.
Note that . actually is a dot (.), so the query could be

Code: Select all

update phpbb_posts set post_text = replace(post_text, 'www.BlaBla', 'forum.BlaBla');
update phpbb_posts set post_text = replace(post_text, 'www.BlaBla', 'forum.BlaBla');

[MetalRush]

Ok, thanks I'll make sure the . is also out of the query.

Do I have to run both queries, by the way?

[HGN]

Yes

[MetalRush]

Oh, I thought of this today... would be nice to let you know that it worked like a charm
Thanks for your assistance: much appreciated

[Kiweed]

Thanks so much for this post ..... worked like a charm for me tooooo