mysql 字符串提取金额_如何从MySQL查询中的字符串提取数值？

返回从字符串最后一个数字：

CREATE FUNCTION getLastNumber(str VARCHAR(255)) RETURNS INT(11)

DELIMETER //

BEGIN

DECLARE last_number, str_length, position INT(11) DEFAULT 0;

DECLARE temp_char VARCHAR(1);

DECLARE temp_char_before VARCHAR(1);

IF str IS NULL THEN

RETURN -1;

END IF;

SET str_length = LENGTH(str);

WHILE position <= str_length DO

SET temp_char = MID(str, position, 1);

IF position > 0 THEN

SET temp_char_before = MID(str, position - 1, 1);

END IF;

IF temp_char BETWEEN '0' AND '9' THEN

SET last_number = last_number * 10 + temp_char;

END IF;

IF (temp_char_before NOT BETWEEN '0' AND '9') AND

(temp_char BETWEEN '0' AND '9') THEN

SET last_number = temp_char;

END IF;

SET position = position + 1;

END WHILE;

RETURN last_number;

END//

DELIMETER;

然后调用这个函数：

选择getLastNumber( “ssss111www222w”); print 222

select getLastNumber(“ssss111www222www3332”); print 3332