MySQL查询从字符串中获取最后三个字符以外的子字符串？

为此，您可以将SUBSTR与length()一起使用。

让我们首先创建一个表-

create table DemoTable
   (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   FirstName varchar(20)
   );

以下是使用insert命令在表中插入一些记录的查询-

insert into DemoTable(FirstName) values('John');
insert into DemoTable(FirstName) values('Carol');
insert into DemoTable(FirstName) values('Robert');
insert into DemoTable(FirstName) values('Chris');
insert into DemoTable(FirstName) values('David');

以下是使用select命令显示表中记录的查询-

select *from DemoTable;

这将产生以下输出-

+----+-----------+
| Id | FirstName |
+----+-----------+
| 1  | John      |
| 2  | Carol     |
| 3  | Robert    |
| 4  | Chris     |
| 5  | David     |
+----+-----------+
5 rows in set (0.00 sec)

以下是查询以获取删除最后3个字符的子字符串-

select substr(FirstName,1,length(FirstName)-3) from DemoTable;

这将产生以下输出-

+-----------------------------------------+
| substr(FirstName,1,length(FirstName)-3) |
+-----------------------------------------+
| J                                       |
| Ca                                      |
| Rob                                     |
| Ch                                      |
| Da                                      |
+-----------------------------------------+
5 rows in set (0.00 sec)

要获得相同的输出，可以使用以下替代查询-

select left(FirstName,length(FirstName)-3) from DemoTable;

这将产生以下输出-

+-------------------------------------+
| left(FirstName,length(FirstName)-3) |
+-------------------------------------+
| J                                   |
| Ca                                  |
| Rob                                 |
| Ch                                  |
| Da                                  |
+-------------------------------------+
5 rows in set (0.00 sec)

要获取最后三个字符，可以使用以下查询-

select substr(FirstName,-3) from DemoTable;

这将产生以下输出-

+----------------------+
| substr(FirstName,-3) |
+----------------------+
| ohn                  |
| rol                  |
| ert                  |
| ris                  |
| vid                  |
+----------------------+
5 rows in set (0.00 sec)