How can we update MySQL table after removing a particular string from the values of column?

We can update MySQL table after removing a particular string from the values of a column by using TRIM() function along with UPDATE clause. Following the example from ‘examination_btech’ table will make it clearer −

Example

Suppose if we want to delete the values ‘(CSE)’, from last, of column ‘Course’ and want to update the table too then it can be done with the help of the following query −

mysql> Update examination_btech SET Course = TRIM(Trailing '(CSE)' FROM Course);
Query OK, 10 rows affected (0.13 sec)

mysql> Select * from examination_btech;
+-----------+----------+--------+
| RollNo    | Name     | Course |
+-----------+----------+--------+
| 201712001 | Rahul    | B.tech |
| 201712002 | Raman    | B.tech |
| 201712003 | Sahil    | B.tech |
| 201712004 | Shalini  | B.tech |
| 201712005 | Pankaj   | B.tech |
| 201712006 | Mohan    | B.tech |
| 201712007 | Yash     | B.tech |
| 201712008 | digvijay | B.tech |
| 201712009 | Gurdas   | B.tech |
| 201712010 | Preeti   | B.tech |
+-----------+----------+--------+
10 rows in set (0.00 sec)

From the above result set, it is clear that ‘(CSE)’ has been removed from last of column ‘course’ and the table also got updated.

Similarly, with the help of TRIM() function, we can remove the string from starting and update the table.

mysql> Update examination_btech SET RollNo = TRIM(Leading '201712' FROM RollNo);
Query OK, 10 rows affected (0.04 sec)

mysql> Select * from examination_btech;
+--------+----------+--------+
| RollNo | Name     | Course |
+--------+----------+--------+
| 1      | Rahul    | B.Tech |
| 2      | Raman    | B.Tech |
| 3      | Sahil    | B.Tech |
| 4      | Shalini  | B.Tech |
| 5      | Pankaj   | B.Tech |
| 6      | Mohan    | B.Tech |
| 7      | Yash     | B.Tech |
| 8      | digvijay | B.Tech |
| 9      | Gurdas   | B.Tech |
| 10     | Preeti   | B.Tech |
+--------+----------+--------+
10 rows in set (0.00 sec)

From the above result set, it is clear that ‘201712’ has been removed from starting of column ‘RollNo’ and the table also got updated.

Arjun Thakur

Published on 21-Feb-2018 10:22:34