在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”。与列值。