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
How can I use MySQL replace() to replace strings in multiple records?
The replace() function can be used to replace a string with another string. To understand replace(), we need to create a table with some records. The following is the query to create a table.
mysql> create table replaceDemo -> ( -> Name varchar(200) -> ); Query OK, 0 rows affected (0.55 sec)
Insert some records with the help of INSERT command. The query to insert records is as follows −
mysql> insert into replaceDemo values('John');
Query OK, 1 row affected (0.17 sec)
mysql> insert into replaceDemo values('Demo');
Query OK, 1 row affected (0.16 sec)
mysql> insert into replaceDemo values('Smith');
Query OK, 1 row affected (0.13 sec)
mysql> insert into replaceDemo values('Demo');
Query OK, 1 row affected (0.14 sec)
mysql> insert into replaceDemo values('Carol');
Query OK, 1 row affected (0.14 sec)
Let us check the records present in the table ‘replaceDemo’. The query is as follows.
mysql> select *from replaceDemo;
The following is an example.
+-------+ | Name | +-------+ | John | | Demo | | Smith | | Demo | | Carol | +-------+ 5 rows in set (0.00 sec)
Above, I have Demo value twice in my table and I want to replace “Demo” with the name ‘Amit’. For that, we can use the replace() function from MySQL. The query is as follows −
mysql> UPDATE replaceDemo -> SET Name = REPLACE (Name, 'Demo', 'Amit') -> WHERE Name LIKE 'Demo'; Query OK, 2 rows affected (0.18 sec) Rows matched: 2 Changed: 2 Warnings: 0
To check whether the name “Demo” has been replaced with the name ‘Amit’ or not, the following is the query.
mysql> select *from replaceDemo;
Here is the output.
+-------+ | Name | +-------+ | John | | Ramit | | Smith | | Amit | | Carol | +-------+ 5 rows in set (0.00 sec)
As you can see in the above output, I have changed the name “Demo” with “Amit”.
913 Views
