I have a column where I need to replace all instances of the text "US-
Complete" (contained within a long sentence) with "US Complete".
There are probably 50 or 100 of them. I'm really scared to do it
since I can't risk screwing up that column - what's the correct syntax?

I could use the same thing... just looking through the documentation there
is a replace() function. Maybe do a backup/restore to a test database before
doing this on your live system...

UPDATE `tablename` SET `fieldname`=REPLACE(tablename.fieldname,'US-
Complete','US Complete') WHERE `fieldname` LIKE '%US- Complete%';





-----Original Message-----
From: Brian Dunning [mailto:brian@briandunning.com]
Sent: Wednesday, 5 September 2007 10:42 AM
To: mysql@lists.mysql.com
Subject: Editing fields in bulk

I have a column where I need to replace all instances of the text "US-
Complete" (contained within a long sentence) with "US Complete".
There are probably 50 or 100 of them. I'm really scared to do it
since I can't risk screwing up that column - what's the correct syntax?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=h...ertainment.com


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.485 / Virus Database: 269.13.5/989 - Release Date: 4/09/2007
5:54 PM


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.485 / Virus Database: 269.13.5/989 - Release Date: 4/09/2007
5:54 PM

Brian Dunning wrote:
> I have a column where I need to replace all instances of the text
> "US-Complete" (contained within a long sentence) with "US Complete".
> There are probably 50 or 100 of them. I'm really scared to do it since I
> can't risk screwing up that column - what's the correct syntax?

UPDATE tbl SET col=REPLACE(col, 'US-Complete', 'US Complete');

If there are spaces in front and back, perhaps you should add some extra
insurance to avoid changing 'US-Completely' or 'BUS-Complete' as well:

UPDATE tbl SET col=REPLACE(col, ' US-Complete ', ' US Complete ');

The best insurance is to do a SELECT first to see what will be changed:

SELECT col, REPLACE(col, 'US-Complete', 'US Complete') FROM tbl;

Baron

Brian Dunning wrote:
> I have a column where I need to replace all instances of the text
> "US-Complete" (contained within a long sentence) with "US Complete".
> There are probably 50 or 100 of them. I'm really scared to do it since
> I can't risk screwing up that column - what's the correct syntax?
>
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=lists@byoteki.com
>
mysql> select * from replacetest;
+----+--------------------------------+
| id | reptest |
+----+--------------------------------+
| 1 | this is a test US-Complete wii |
| 2 | look US-Complete is here |
| 3 | Fun test |
+----+--------------------------------+
3 rows in set (0.00 sec)

mysql> update replacetest set reptest=replace(reptest, 'US-Complete',
'US Complete') where reptest like '%US-Complete%';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from replacetest;
+----+--------------------------------+
| id | reptest |
+----+--------------------------------+
| 1 | this is a test US Complete wii |
| 2 | look US Complete is here |
| 3 | Fun test |
+----+--------------------------------+
3 rows in set (0.00 sec)

> I have a column where I need to replace all instances of the text "US-
> Complete" (contained within a long sentence) with "US Complete".
> There are probably 50 or 100 of them. I'm really scared to do it
> since I can't risk screwing up that column - what's the correct syntax?

When you screw up, that's when you do a "rollback". Welcome to
transactions.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com