mysql 字符串中提取数字

一、先创建函数

CREATE FUNCTION get_number (param varchar(50))

RETURNS varchar(30)

BEGIN
DECLARE length INT DEFAULT 0;

DECLARE temp_str varchar(50) default '';

set length=CHAR_LENGTH(param);

WHILE length > 0 DO

IF (ASCII(mid(param,length,1))>47 and ASCII(mid(param,length,1))<58 )THEN

set temp_str = concat(temp_str,mid(param,length,1));

END IF;

SET length = length - 1;

END WHILE;

RETURN REVERSE(temp_str);

END;

创建测试表：

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', '杭上0001人调字第X号');
INSERT INTO `test` VALUES ('2', '杭上清0005人调字第X号');

调用自定义函数：

SELECT  get_number(name) from test;