mySQL query to mass change all instances of a particular word.

I was wondering if anybody could show me a quick query to change all instances in the database from tony to TONY (all caps)

 

Not sure of the best way, but you could generate a data script using mySQL administrator. Once you have that script, open it in your mySQL Query Browser, then to a search and replace for tony, checking the case sensitive option.

Then run the script.

 

maybe this php script can give you an idea.

<?php

$query = mysql_query("SELECT * FROM `db_table`");
while($row = mysql_fetch_array($query))
{
	mysql_query("UPDATE `db_table` SET `name` = 'TONY' WHERE `name` = 'tony'");
}

?>

Code (markup):

 

If you can run SQL directly against the database then you could use something like:

update tablename set fieldname = case fieldname when 'tony' then 'TONY' else fieldname end;

Backup/export the table first - this is very unforgiving of finger trouble!

 

The following query will replace tony with TONY in the specified field.

UPDATE table SET 
field_name = REPLACE(field_name, 'tony', 'TONY')

Code (markup):

Before running this query, backup your table.

 

I think he wants to replace all occurrences in all fields of all tables though.

 

You can export the data. Edit the data in a text Editor to carry out search and replace,
and then re-import the data.

 

You know, I actually found some indexing tricks that worked. Woowhoo.. thanks for all the help!

 

Can you share that to us? I'm also looking for a query like that.