How can we update MySQL table after padding a string with the values of the column?

MySQLMySQLi Database

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

Example

Suppose if we want to append the values, in last, of column course with the string ‘(CSE)’ 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 = RPAD(Course, 11,'(CSE)');
Query OK, 10 rows affected (0.16 sec)

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

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

Similarly, with the help of LPAD() function, we can pad the string in starting and update the table.

raja
Published on 21-Feb-2018 11:33:29
Advertisements