MySql 字符串中提取数字-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySql 字符串中提取数字。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、前言

在MySQL中，有时需要从字符串中提取数字。在这种情况下，我们可以使用一些函数来实现该效果。但是，这些函数中的一些可能会提取字符串中的所有数字，而其他函数可能只会提取最长的数字字符序列。因此，我们需要了解每个函数的行为及其应用场景。

二、mysql 内置函数

1. REGEXP_REPLACE()函数

REGEXP_REPLACE()函数可以用于从字符串中删除任何不是数字的字符，留下数字字符。以下是一个例子：

SELECT REGEXP_REPLACE("abc123def456", '[^0-9]', '');
// Output: 123456

其中的正则表达式[^0-9]表示是否出现除0-9以外的字符，替换掉原来的字符串中的所有非数字字符。

2. REGEXP_SUBSTR()函数

REGEXP_SUBSTR()函数可以用于从字符串中提取匹配正则表达式的字串。以下是一个例子：

SELECT REGEXP_SUBSTR("name123_phone456", '[0-9]+');
// Output: 123

其中的正则表达式[0-9]+表示会选择字符串中的一个或多个数字。

3. SUBSTRING()函数结合正则表达式

另一种方法是使用SUBSTRING()函数和正则表达式，可以从字符串中提取数字序列。以下是一个例子：

SELECT SUBSTRING("abc123def456",LOCATE("123", "abc123def456"), 
    LOCATE("456", "abc123def456")-LOCATE("123", "abc123def456")+1);
// Output: 123def456

其中LOCATE()函数用于找到字符串中某个子字符串的位置，然后使用SUBSTRING()函数从该位置开始提取包含在“123”和“456”之间的字符串。如果你想要提取数字序列，请使用正则表达式。

三、自定义函数

下面是一个基于循环的MySQL自定义函数，用于从输入的字符串中提取所有数字字符并将其组合成一个整数：

一、函数1

DROP FUNCTION IF EXISTS `number_extract`;
DELIMITER $$
CREATE FUNCTION `number_extract`(string_inport VARCHAR(255)) 
RETURNS INT
NO SQL
BEGIN
    DECLARE ctrNumber VARCHAR(255);
    DECLARE finNumber VARCHAR(255) DEFAULT '';
    DECLARE sChar VARCHAR(1);
    DECLARE inti INTEGER DEFAULT 1;
    IF LENGTH(string_inport) > 0 THEN
        WHILE(inti <= LENGTH(string_inport)) DO
            SET sChar = SUBSTRING(string_inport, inti, 1);
            SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9'); 
            IF ctrNumber > 0 THEN
                SET finNumber = CONCAT(finNumber, sChar);
            END IF;
            SET inti = inti + 1;
        END WHILE;
        RETURN CAST(finNumber AS UNSIGNED);
    ELSE
        RETURN 0;
    END IF;    
END$$
DELIMITER ;

该函数接受一个VARCHAR(255)类型的字符串参数，并返回一个整数值。函数使用了一个while循环来遍历字符串中的每个字符。如果字符是数字，则将其添加到finNumber字符串中。在循环结束后，使用MySQL内置的CAST()函数将finNumber字符串转换为整数类型。

二、函数2

如果需要从输入的字符串中提取特定数量的连续数字字符并将其组合成一个整数，我们可以使用两个自定义函数。

DROP FUNCTION IF EXISTS `number_extract_n1`;
DELIMITER $$
CREATE FUNCTION `number_extract_n1`(string_inport VARCHAR(255), num_digits INT) 
RETURNS INT
NO SQL
BEGIN
    DECLARE ctrNumber VARCHAR(255);
    DECLARE finNumber VARCHAR(255) DEFAULT '';
    DECLARE sChar VARCHAR(1);
    DECLARE inti INTEGER DEFAULT 1;
    IF LENGTH(string_inport) > 0 AND num_digits > 0 THEN
        WHILE(inti <= LENGTH(string_inport)) DO
            SET sChar = SUBSTRING(string_inport, inti, 1);
            SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9'); 
            IF ctrNumber > 0 THEN
                SET finNumber = CONCAT(finNumber, sChar);
                IF LENGTH(finNumber) = num_digits THEN
                    SET inti = LENGTH(string_inport)+1;
                END IF;
            END IF;
            SET inti = inti + 1;
        END WHILE;
        RETURN CAST(finNumber AS UNSIGNED);
    ELSE
        RETURN 0;
    END IF;    
END$$
DELIMITER ;

该函数接受两个参数，一个VARCHAR(255)类型的字符串和一个INT类型的数字。函数返回一个整数，该整数由输入字符串中的指定数量的连续数字字符组成。

函数遍历输入的字符串并扫描每个字符，如果字符是数字，则将其添加到finNumber字符串中。在finNumber中包含所需数量的数字字符时，函数将循环终止并返回finNumber转换为整数类型的值。

三、函数3

DROP FUNCTION IF EXISTS `number_extract_n2`;
DELIMITER $$
CREATE FUNCTION `number_extract_n2`(string_inport VARCHAR(255), start_idx INT, num_digits INT) 
RETURNS INT
NO SQL
BEGIN
    DECLARE ctrNumber VARCHAR(255);
    DECLARE finNumber VARCHAR(255) DEFAULT '';
    DECLARE sChar VARCHAR(1);
    DECLARE inti INTEGER DEFAULT start_idx;
    DECLARE end_idx INTEGER DEFAULT start_idx + num_digits;
    IF LENGTH(string_inport) > 0 AND num_digits > 0 AND end_idx <= LENGTH(string_inport) THEN
        WHILE(inti <= end_idx) DO
            SET sChar = SUBSTRING(string_inport, inti, 1);
            SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9'); 
            IF ctrNumber > 0 THEN
                SET finNumber = CONCAT(finNumber, sChar);
            ELSE
                RETURN 0;
            END IF;
            SET inti = inti + 1;
        END WHILE;
        RETURN CAST(finNumber AS UNSIGNED);
    ELSE
        RETURN 0;
    END IF;    
END$$
DELIMITER ;

该函数接受三个参数，一个VARCHAR(255)类型的字符串，一个INT类型的数字表示起始索引值，一个INT类型的数字表示要提取的数字字符的数量。

函数遍历输入字符串的子字符串，并扫描每个字符，如果字符是数字，则将其添加到finNumber字符串中。如果finNumber包含所需数量的数字字符，则函数将返回finNumber字符串转换为整数类型的值。如果没有提取到足够数量的连续数字字符，函数将返回0。