mysql how to replace a column content

Question

I using Mysql.

Table: Content_T1
id : int
title : varchar255
content: text

my all page content will be save in content columns.

mysql content column... there is some <img src="http://abc.com/data/img/......">

But I had change my domain,

so I need change all <img> tag

if url = http://www.abc.com/data/img/....

I need change to = http://www.def.com/data/img/....

Just replace this url and path, without affecting other content.

How can I do ?

Answer 1

Let's say you want to update id 27

Run this first

SELECT REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img') newurl
FROM Content_T1 WHERE id = 27;

If the result is correct, you can run

UPDATE Content_T1
SET content = REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img')
WHERE id = 27;
SELECT content FROM Content_T1 WHERE id = 27;

If the result is correct and you are ready to fix all of them, run this query without the WHERE clause:

UPDATE Content_T1
SET content = REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img');

Please, go to a test server and run this. When the results are correct ...

GIVE IT A TRY !!!