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 05:52:34

• Related Questions & Answers
• How can we update MySQL table after padding a string with the values of the column?
• How can I update MySQL table after quoting the values of a column with a single quote?
• How can we update values in a MySQL table?
• How can we update the values in one MySQL table by using the values of another MySQL table?
• How can we update any value in MySQL view as we can update the values in MySQL table?
• How can we fetch all the records from a particular MySQL table?
• How can we remove a column from MySQL table?
• How can we fetch a particular row as output from a MySQL table?
• How can we extract a substring from the value of a column in MySQL table?
• How can we count a number of unique values in a column in MySQL table?
• Can we add a column to a table from another table in MySQL?
• How can we add values into the columns of a MySQL table?
• How can we modify column/s of MySQL table?
• How to update a MySQL table by swapping two column values?
• How we can find all the triggers associated with a particular MySQL table?