Delete last 4 letters in MySQL?

MySQLMySQLi Database

You can use SUBSTRING() along with UPDATE command to delete last 4 letters. Let us first create a table −

mysql> create table DemoTable
(
   StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   StudentSubject varchar(100)
);
Query OK, 0 rows affected (0.57 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(StudentSubject) values('Introduction to Java');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(StudentSubject) values('Introduction to C');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(StudentSubject) values('Introduction to C++');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(StudentSubject) values('Spring And Hibernate');
Query OK, 1 row affected (0.13 sec)

Following is the query to display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-----------+----------------------+
| StudentId | StudentSubject       |
+-----------+----------------------+
| 1         | Introduction to Java |
| 2         | Introduction to C    |
| 3         | Introduction to C++  |
| 4         | Spring And Hibernate |
+-----------+----------------------+
4 rows in set (0.00 sec)

Following is the query to delete last 4 letters −

mysql> update DemoTable set StudentSubject=SUBSTRING(StudentSubject, 1, LENGTH(StudentSubject)-4) ;
Query OK, 4 rows affected (0.16 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Let us display all records from the table to check the last 4 letters have been deleted or not −

mysql> select *from DemoTable;

This will produce the following output −

+-----------+------------------+
| StudentId | StudentSubject   |
+-----------+------------------+
| 1         | Introduction to  |
| 2         | Introduction     |
| 3         | Introduction to  |
| 4         | Spring And Hiber |
+-----------+------------------+
4 rows in set (0.00 sec)

Yes, the last 4 letters have been deleted successfully.

raja
Published on 29-Apr-2019 07:52:57
Advertisements