MySQL查询用目录链接替换最后一个/列中的字符串？
为此，请使用substring_index()方法。让我们首先创建一个表-
mysql> create table DemoTable -> ( -> FolderName varchar(100), -> FolderLocation varchar(200) -> );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('CProgram','C:/AllPrograms/.....');
mysql> insert into DemoTable values('Images','E:/MyImage/home/garbage');使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
输出结果
+------------+-------------------------+ | FolderName | FolderLocation | +------------+-------------------------+ | CProgram | C:/AllPrograms/..... | | Images | E:/MyImage/home/garbage | +------------+-------------------------+ 2 rows in set (0.00 sec)
这是用目录链接替换列中最后一个/后面的字符串的查询-
mysql> UPDATE DemoTable SET FolderLocation = CONCAT(LEFT(FolderLocation , CHAR_LENGTH(FolderLocation ) - CHAR_LENGTH(SUBSTRING_INDEX(FolderLocation , '/', -1))),FolderName); Rows matched: 2 Changed: 2 Warnings: 0
让我们再次检查表记录-
mysql> select *from DemoTable;
输出结果
+------------+-------------------------+ | FolderName | FolderLocation | +------------+-------------------------+ | CProgram | C:/AllPrograms/CProgram | | Images | E:/MyImage/home/Images | +------------+-------------------------+ 2 rows in set (0.00 sec)