Does MySQL update with regexp possible?

MySQLMySQLi Database

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)
raja
Published on 26-Feb-2019 07:24:24
Advertisements