Update a column value, replacing part of a string in MySQL?

To update a column value, the update command as well as the replace method can be used. The steps to better understand these are given as follows −

First create a table with the help of the create command. This is given as follows −

mysql> CREATE table DemoOnReplace
-> (
-> Id int,
-> Name varchar(200)
-> );
Query OK, 0 rows affected (0.63 sec)

After successfully creating a table, some records are inserted with the help of the insert command. This is shown below −

mysql> INSERT into DemoOnReplace values(1,'John');
Query OK, 1 row affected (0.10 sec)

mysql> INSERT into DemoOnReplace values(1,'David warner');
Query OK, 1 row affected (0.15 sec)

Now, all the table records can be displayed with the help of the select command which is given as follows −

mysql> SELECT * from DemoOnReplace;

After executing the above query, the following output is obtained −

+------+--------------+
| Id   | Name         |
+------+--------------+
| 1    | John         |
| 1    | David warner |
+------+--------------+
2 rows in set (0.00 sec)

The syntax to update a column value is as follows −

UPDATE yourTableName set column_name = REPLACE(column_name , 'oldvalue', 'NewValue');

The above syntax is used to replace part of the string with update command. This is given as follows −

mysql> UPDATE DemoOnReplace set name = REPLACE(name, 'David warner', 'David warner S.');
Query OK, 1 row affected (0.18 sec)
Rows matched: 2 Changed: 1 Warnings: 0

The output of the above query is as follows −

+------+-----------------+
| Id   | Name            |
+------+-----------------+
| 1    | John            |
| 1    | David warner S. |
+------+-----------------+
2 rows in set (0.00 sec)

As can be seen from the above output, a part of the string is replaced by updating a column value.

Chandu yadav

Published on 18-Oct-2018 09:26:35

• Related Questions & Answers
• Sort a MySQL table column value by part of its value?
• Update only a single column value in MySQL
• Replace part of string in MySQL table column?
• Get part of a string based on a character in MySQL?
• Split the left part of a string by a separator string in MySQL?
• Update a column of text with MySQL REPLACE()
• How to cast and update a numeric value from string column only where applicable in MySQL?
• How to cut part of a string with a MySQL query?
• How to update a MySQL column by subtracting a value with some conditions?
• How to prepend a string to a column value in MySQL?
• Replacing Substrings in a Java String
• How to update MySQL column with random value?
• Update a MySQL column with JSON format?
• How to order by certain part of a string in MySQL?
• Fetch middle part of a string surrounded by slash in MySQL