In MySQL, how to remove a specific prefix from an entire column's values ​​and update them?

This can be accomplished by applying the TRIM() function on the column along with the MySQL UPDATE statement. The following example will illustrate this point more clearly.

Example

Suppose we have a table "Employee" with prefix "Dept." which contains all the values ​​of column "Department" as follows -

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)

Now, The following query will remove the prefix "Dept." from the "Department" column and update the table.

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)

The above result set shows that there is no prefix "Dept." in the column value.

The above is the detailed content of In MySQL, how to remove a specific prefix from an entire column's values ​​and update them?. For more information, please follow other related articles on the PHP Chinese website!