mysql获取数字

1、定义函数

DROP FUNCTION IF EXISTS `GetNumber`;
DELIMITER $$
CREATE FUNCTION `GetNumber`(in_string VARCHAR(50), in_index INTEGER) 
RETURNS INT
NO SQL
BEGIN
    DECLARE ctrNumber VARCHAR(50);
    DECLARE finNumber VARCHAR(50) DEFAULT '';
    DECLARE sChar VARCHAR(1);
    DECLARE inti INTEGER DEFAULT 1;
    DECLARE tmp_index INTEGER DEFAULT 0;
    DECLARE tmp_continuous TINYINT DEFAULT 1;
    DECLARE tmp_find TINYINT DEFAULT 0;
    IF LENGTH(in_string) > 0 THEN
        WHILE(inti <= LENGTH(in_string)) DO
  
            SET sChar = SUBSTRING(in_string, inti, 1);
            SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9'); 
            IF ctrNumber > 0 THEN
                IF in_index > 0 THEN
                    IF inti = 1 THEN
                        SET tmp_index = tmp_index + 1;
                    ELSEIF tmp_continuous = 0 THEN
                        SET tmp_index = tmp_index + 1;
                        SET tmp_continuous = 1;
                    END IF;

                    IF tmp_index = in_index THEN
                        SET finNumber = CONCAT(finNumber, sChar);
                        SET tmp_find = 1;
                    END IF;
                ELSE
                    SET finNumber = CONCAT(finNumber, sChar);
                END IF;
            ELSE
                IF in_index > 0 THEN
                    IF tmp_find = 1 THEN
                        SET inti = LENGTH(in_string);
                    ELSE
                        SET tmp_continuous = 0;
                        SET finNumber = '';
                    END IF;
                END IF;
            END IF;
            SET inti = inti + 1;

        END WHILE;

        IF finNumber = '' THEN
            RETURN 0;
        ELSE
            RETURN CAST(finNumber AS UNSIGNED);
        END IF;
        
    ELSE
        RETURN 0;
    END IF;    
END$$
DELIMITER ;

2、使用

mysql> select GetNumber('1222df3f43fdg76df', 0);
+-----------------------------------+
| GetNumber('1222df3f43fdg76df', 0) |
+-----------------------------------+
|                         122234376 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select GetNumber('1222df3f43fdg76df', 1);
+-----------------------------------+
| GetNumber('1222df3f43fdg76df', 1) |
+-----------------------------------+
|                              1222 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select GetNumber('1222df3f43fdg76df', 2);
+-----------------------------------+
| GetNumber('1222df3f43fdg76df', 2) |
+-----------------------------------+
|                                 3 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select GetNumber('1222df3f43fdg76df', 3);
+-----------------------------------+
| GetNumber('1222df3f43fdg76df', 3) |
+-----------------------------------+
|                                43 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select GetNumber('1222df3f43fdg76df', 4);
+-----------------------------------+
| GetNumber('1222df3f43fdg76df', 4) |
+-----------------------------------+
|                                76 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select GetNumber('1222df3f43fdg76df', 5);
+-----------------------------------+
| GetNumber('1222df3f43fdg76df', 5) |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

说明
1、函数第一参数（in_string）为目标字符，第二参数（in_index）为检索第几批数字（连续数字为一批）
2、第二参数（in_index）小于等于0，表示获取所有数字（不连续），大于0表示获取对应批次的数字（检索大于实际拥有的批次，返回0）
3、找不到统一返回0

参考