MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Victor Pendleton Date:July 26 2004 1:07pm
Subject:RE: String substitution in MYSQL
View as plain text  
Try using the REPLACE command.

-----Original Message-----
From: Adaikalavan Ramasamy
To: mysql@stripped
Sent: 7/26/04 8:04 AM
Subject: String substitution in MYSQL

I am looking for a string substitution command in MYSQL and was
wondering if anyone can help me. Searching the archives was not
fruitful. Here is an example :

  CREATE TABLE tbl (id INT(2), names VARCHAR(20));
  INSERT INTO tbl VALUES (1, 'aaa');
  INSERT INTO tbl VALUES (2, 'bbb; aaa; ccc');
  SELECT * FROM tbl;
+------+---------------+
| id   | names         |
+------+---------------+
|    1 | aaa           |
|    2 | bbb; aaa; ccc |
+------+---------------+

Now, suppose that I want to change all 'aaa' into 'zzz'. The following
command works for id 1 but not id 2.
  UPDATE tbl SET names='zzz' WHERE names='aaa';

QUESTION : How do I change 'bbb; aaa; ccc' -> 'bbb; zzz; ccc' ?

These do not work either :
  UPDATE tbl SET names='zzz' WHERE names like "%aaa%";
  UPDATE tbl SET names="%zzz%" WHERE names like "%aaa%";

I could do this in Perl but prefer to do it in MYSQL for code brevity
and speed. 

Thank you.

Regards, 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
Thread
String substitution in MYSQLAdaikalavan Ramasamy26 Jul
RE: String substitution in MYSQLVictor Pendleton26 Jul
  • RE: String substitution in MYSQLAdaikalavan Ramasamy26 Jul