- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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”.
- Related Articles
- How MySQL REPLACE() function replaces strings in multiple records?
- Replace records based on conditions in MySQL?
- How can I replace & with an ampersand in my MySQL database?
- How can MySQL find and replace the data with REPLACE() function to UPDATE the table?
- How can we use MySQL REPLACE statement to prevent of insertion of duplicate data?
- Find and replace a part of URL records in MySQL?
- How can I find and replace in MySQL in a column with file path?
- How to use replace () in Android textview?
- How do I replace “+”(plus sign) with SPACE in MySQL?
- How i can replace number with string using Python?
- How to use REPLACE() function with column’s data of MySQL table?
- MySQL search and replace record from a list of records
- How to use replace command in Android sqlite?
- MySQL: How can I find a value with special character and replace with NULL?