MySQL Update with REGEXP: Is It Possible?

You cannot update with regexp i.e. you need to use LIKE operator instead of regexp. MySQL does not provide support for update with regexp. The LIKE operator is as follows:

UPDATE yourTableName SET yourColumnName= REPLACE(yourColumnName,yourValue)', '' )
WHERE yourColumnNameLIKE '%yourValueThatWillReplace)%';

To understand the above syntax, let us create a table.

mysql> create table Replace_Demo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Value varchar(20),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into Replace_Demo(Value) values('221)');
Query OK, 1 row affected (0.22 sec)
mysql> insert into Replace_Demo(Value) values('321');
Query OK, 1 row affected (0.24 sec)
mysql> insert into Replace_Demo(Value) values('354)');
Query OK, 1 row affected (0.18 sec)
mysql> insert into Replace_Demo(Value) values('223)');
Query OK, 1 row affected (0.09 sec)
mysql> insert into Replace_Demo(Value) values('446');
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from Replace_Demo;

The following is the output:

+----+-------+
| Id | Value |
+----+-------+
|  1 | 221)  |
|  2 | 321   |
|  3 | 354)  |
|  4 | 223)  |
|  5 | 446   |
+----+-------+
5 rows in set (0.00 sec)

Here is the query to update with LIKE operator in place of regexp:

mysql> update Replace_Demo set Value = REPLACE(Value,'221)', '' )
   -> WHERE Value like '%221)%';
Query OK, 1 row affected (0.21 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now check the table records once again using select statement. The query is as follows:

mysql> select *from Replace_Demo;

The following is the output:

+----+-------+
| Id | Value |
+----+-------+
|  1 |       |
|  2 | 321   |
|  3 | 354)  |
|  4 | 223)  |
|  5 | 446   |
+----+-------+
5 rows in set (0.00 sec)