在MySQL中，如何从整个列的值中删除特定的前缀并更新它们？
可以通过TRIM()在列上应用函数以及MySQLUPDATE语句来完成。下面的示例将使其更加清晰。
示例
假设我们有一个表“Employee”，其前缀为“Dept”。具有“部门”列的所有值，如下所示：
mysql> Select * from Employee; +------+----------------+------------+----------------------+ | Id | Name | Address | Department | +------+----------------+------------+----------------------+ | 100 | Raman | Delhi | IT Dept. | | 101 | Mohan | Haryana | History Dept. | | 102 | Shyam | Chandigarh | ENGLISH Dept. | | 103 | Sukhjeet Singh | Patiala | Computer Engg. Dept. | | 104 | Bimal Roy | Calcutta | Computer Engg. Dept. | +------+----------------+------------+----------------------+ 5 rows in set (0.01 sec)
现在，下面的查询将删除前缀“Dept”。从列处，并更新表。
mysql> Update Employee set Department = TRIM(Trailing 'Dept.' FROM Department); Rows matched: 5 Changed: 5 Warnings: 0 mysql> Select * from Employee; +------+----------------+------------+-----------------+ | Id | Name | Address | Department | +------+----------------+------------+-----------------+ | 100 | Raman | Delhi | IT | | 101 | Mohan | Haryana | History | | 102 | Shyam | Chandigarh | ENGLISH | | 103 | Sukhjeet Singh | Patiala | Computer Engg. | | 104 | Bimal Roy | Calcutta | Computer Engg. | +------+----------------+------------+-----------------+ 5 rows in set (0.00 sec)
上面的结果集显示没有前缀“Dept”。与列值。
热门推荐
10 考试祝福语简短给对象
11 祝福语对联文案简短大气
12 最美的留言祝福语简短
13 简短正能量生日祝福语
14 送给情侣贺卡祝福语简短
15 祝福语生日男朋友简短
16 学霸毕业祝福语简短
17 朋友明天手术祝福语简短
18 鱼的内涵简短祝福语