从MySQL列中删除括号内的尾随数字
为此，请trim()与一起使用substring()。让我们首先创建一个表-
mysql> create table DemoTable ( Name varchar(100) );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('1stJohn');
mysql> insert into DemoTable values('1stJohn (7)');
mysql> insert into DemoTable values('2ndSam');
mysql> insert into DemoTable values('2ndSam (4)');使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+-------------+ | Name | +-------------+ | 1stJohn | | 1stJohn (7) | | 2ndSam | | 2ndSam (4) | +-------------+ 4 rows in set (0.00 sec)
以下是从MySQL列中删除括号内的尾随数字的查询-
mysql> select trim(substring(Name, 1, (CHAR_LENGTH(Name) - LOCATE('(', REVERSE(Name))))) AS RemovingTrailingNumbers from DemoTable;这将产生以下输出-
+-------------------------+ | RemovingTrailingNumbers | +-------------------------+ | 1stJohn | | 1stJohn | | 2ndSam | | 2ndSam | +-------------------------+ 4 rows in set (0.00 sec)