Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Does MySQL update with regexp 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)
Updated on: 30-Jul-2019
213 Views
Advertisements