场景:
在MySQL处理数据时,要取得数据库字段中文件名的扩展名,需要对数据库字段做lastIndexOf截断
方法一:
SELECT SUBSTRING_INDEX('test.htm','.',-1)
方法二:
SELECT REVERSE(LEFT(REVERSE('test.htm'),INSTR(REVERSE('test.htm'),'.')))
方法三:
DELIMITER $$
DROP FUNCTION IF EXISTS `test`.`lastIndexOf`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `fun_LastIndexOf`(str varchar(100)) RETURNS int(11)
begin
DECLARE num int default 0;
DECLARE strs varchar(100);
set strs = str;
while instr(strs,'.') != 0 do
set num =num+instr(strs,'.');
set strs = substring(strs,instr(strs,'.')+1);
end while;
return num;
end$$
DELIMITER ;
SELECT MAX(CAST(substring(name,fun_LastIndexOf(name)+1) AS DECIMAL ))
FROM table_name
WHERE name = 'test.htm'