[Resolved] update part of a field in lots of rows

[gushy]

I've got a MySQL database I've just transferred onto a new machine.

It's an old databse and I've now noticed a slight flaw in my design. It holds information on files and I've recorded their location with a full path.

Only problem is the path is different on the new server.

How can I update lots of records but only change part of a field?

ie:
/home/blah/files/xyz.txt -> /var/storage/blah/yxz.txt
/home/blah/files/abc/txt -> /var/storage/blah/abc.txt

I can do this in PHP no problem, but I'm looking for a MySQL solution to this.

Anybody?

[barand]

A browse through mySql string functions turned up this

REPLACE(str,from_str,to_str)
Returns the string str with all all occurrences of the string from_str replaced
by the string to_str:
mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
This function is multi-byte safe.

hth

[gushy]

looks great thanx. I'll try it now.

[gushy]

Yeah that did it. 😃 I used the following statement:
UPDATE table SET field = REPLACE(table.field,'blah','foobar');