sql函数实现从字段字符串中获取数字

sql函数实现从字段字符串中获取数字

BEGIN
DECLARE v_length INT DEFAULT 0;
DECLARE v_Tmp varchar(50) default ”;
DECLARE v_result varchar(50) default ”;
DECLARE v_str1 varchar(50) default ”;
DECLARE v_str2 varchar(50) default ”;
DECLARE v_temp INT DEFAULT 1;
set v_length=CHAR_LENGTH(Varstring);
WHILE v_length > 0 DO
IF (ASCII(mid(Varstring,v_length,1))>47 and ASCII(mid(Varstring,v_length,1))<58 OR ASCII(mid(Varstring,v_length,1))=46) THEN
set v_Tmp=concat(v_Tmp,mid(Varstring,v_length,1));
set v_temp=1;
ELSEIF v_temp!=0 THEN
set v_Tmp = CONCAT(v_Tmp,’|’);
set v_temp = 0;
END IF;
SET v_length = v_length - 1;
END WHILE;
set v_result = REVERSE(v_Tmp);
set v_str1 = substring_index(substring_index(v_result,’|’,2),’|’,-1);
set v_str2 = substring_index(substring_index(v_result,’|’,-2),’|’,1);
set v_result = v_str2/v_str1;
RETURN v_result;
END