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.