Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies在 MySQL 中如何移除整列中的特定字首並更新這些行？
可以透過將 `TRIM()` 函式與 MySQL `UPDATE` 語句一起應用於列來實現。下面的示例將使它更加清晰。
示例
假設，我們有一個表“Employee”，它在列“Department”的所有值前都有字首“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)
現在，下面的查詢將從列 `Department` 中刪除字首 `Dept.`，並且還會更新表。
mysql> Update Employee set Department = TRIM(Trailing 'Dept.' FROM Department); Query OK, 5 rows affected (0.10 sec) 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.”。
更新於： 06-Feb-2020
1K+ 瀏覽
廣告