如何在移除列值中的特定字串後更新MySQL表?
我們可以使用TRIM()函式以及UPDATE子句來更新MySQL表,並在列值中移除特定字串。以下來自‘examination_btech’表的示例將使它更清晰:
示例
假設我們想要從‘Course’列的末尾刪除值‘(CSE)’,並且也希望更新表,那麼可以使用以下查詢來實現:
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)
從以上結果集中,可以清楚地看到‘(CSE)’已從‘course’列的末尾刪除,並且表也已更新。
類似地,藉助TRIM()函式,我們可以從開頭刪除字串並更新表。
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)
從以上結果集中,可以清楚地看到‘201712’已從‘RollNo’列的開頭刪除,並且表也已更新。
廣告