Updating Substring in Column in MySQL

Today I needed to make the same kind of update to a field in a few rows of a large database table. There were too many rows to do it with a separate SQL update statement per row, so I hunted down the MySQL REPLACE function. Okay, it was only 6 rows, but I needed to make the same change in a dev, QA and production database. The example in the documentation is pretty trivial, but it wasn’t too hard to expand it to conditionally replace the substring.

mysql> help replace;
Name: 'REPLACE'
Description:
Syntax:
REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str
replaced by the string to_str. REPLACE() performs a case-sensitive
match when searching for from_str.

URL: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

Examples:
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

The UPDATE query below is pretty similar to what I needed to do, i.e., remove some characters from the end of a varchar column. The LIKE clause I’ve used in the query ensures that “cruft” is replaced only if it appears at the end of the string.

mysql> CREATE TABLE tbl (col varchar(20));
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT INTO tbl VALUES ('cruft_front'), ('some_cruft_in_middle'), ('end_cruft');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE tbl SET col = REPLACE(col, 'cruft', '') WHERE col LIKE '%cruft';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tbl;
+----------------------+
| col                  |
+----------------------+
| cruft_front          | 
| some_cruft_in_middle | 
| end_                 | 
+----------------------+
3 rows in set (0.00 sec)