自己写了个函数,只能取整数最大值,小数有点问题,不过已经满足我的需求了,贴出来给大家分享下。
CREATE DEFINER=`root`@`localhost` FUNCTION `FindMaxNumber`(_input VARCHAR(64)) RETURNS float
BEGIN
DECLARE _r VARCHAR(32) DEFAULT '';
DECLARE _i INTEGER DEFAULT 1;
DECLARE _t BOOLEAN DEFAULT TRUE;
DECLARE _start INTEGER DEFAULT 0;
DECLARE _IsCharNumeric BOOLEAN;
DECLARE _maxnum FLOAT DEFAULT 0;
DECLARE _num FLOAT DEFAULT 0;
WHILE _i < length(_input) DO
_loop: REPEAT
SET _IsCharNumeric = locate(mid(_input, _i, 1), ".0123456789") > 0;
IF _IsCharNumeric THEN
IF _t THEN
SET _t = FALSE;
SET _start = _i;
END IF;
ELSE
IF _start > 0 THEN LEAVE _loop; END IF;
END IF;
SET _i = _i + 1;
UNTIL _i > length(_input) END REPEAT;
IF _start > 0 AND NOT _t THEN
SET _t = TRUE;
SET _r = mid(_input, _start, _i - _start);
SET _num = convert(_r,DECIMAL);
SET _maxnum = greatest(_maxnum,_num);
END IF;
SET _i = _i+1;
END WHILE;
RETURN _maxnum;
END