Replacing text in a mysql table

[turbohost]

Hi,

Haven't found an answer to this one yet. How can I replace a string in a column by another string in a mysql table? For example, I've got a table called program with 2 columns called program_id and program_name. Some fields in the column program_name contain the string 'some text F/ some text' and I want to replace this with 'some text Ft some text'. Can I do this via command line?

Turbohost

[timster]

Well, here's how I figured out how to do this, but I reckon there must be a way to do this with one query.

$sql = "SELECT id, field_to_update FROM table_name WHERE field_to_update REGEXP '$pattern'";
$rs = mysql_query($sql);

while ($row = mysql_fetch_array($rs)) {

$id = $row[0];
$string = preg_replace("/$pattern/",$replacement,$row[1]);
$sql = "UPDATE table_name SET field_to_update = '$string' WHERE ID = '$id'";
mysql_query($sql);
}

[turbohost]

Anyone else?

[nalin]

mysql has string functions - [dev.mysql.com...]

In your example
update table_name set field_name = replace(field_name,"f/","ft") where field REGEXP('f/')