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。

四、后语

我们介绍了三个自定义函数以及MySQL内置函数REGEXP_REPLACE()、REGEXP_SUBSTR()和SUBSTRING()，这些函数可以从输入的字符串中提取数字。我们还了解了每个函数的行为及其应用场景。

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

REGEXP_SUBSTR()函数可用于从字符串中提取匹配正则表达式的字串。

SUBSTRING()函数结合正则表达式可以从字符串中提取数字序列。

自定义函数1可以从字符串中提取所有数字字符并将其组合成一个整数。

自定义函数2和3可用于从输入的字符串中提取连续数字字符，并将其组合成一个指定数量的整数。

选择适当的函数来提取数字字符是十分重要的，取决于我们需要哪些数字字符以及是否必须是连续的数字字符。在实际应用中，应该根据实际情况进行选择，以便快速高效地实现所需的结果。