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

Previous Page Print Page