mysql获取数字

最新推荐文章于 2024-07-28 02:54:51 发布

协手共建美好家园

最新推荐文章于 2024-07-28 02:54:51 发布

阅读量912

点赞数

分类专栏：笔记数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/XIYECODE/article/details/120087356

版权

笔记同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

数据库

8 篇文章 0 订阅

订阅专栏

本文介绍了一个MySQL自定义函数`GetNumber`，该函数用于从字符串中按指定位置提取连续的数字。函数接受两个参数，一个是目标字符串，另一个是检索的数字批次。当检索批次小于等于0时，返回所有不连续的数字；大于0时，返回对应批次的连续数字。示例用法展示了如何根据不同的批次参数获取不同的数字组合。

摘要由CSDN通过智能技术生成

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 |
+-----------------------------------+