MySQL 如何从MySQL字符串中提取数字部分？

在MySQL中，字符串和数字之间经常需要进行相互转换的操作。其中，提取字符串中的数字部分也是经常遇到的问题。本文将会介绍如何从MySQL字符串中提取数字的方法。

阅读更多：MySQL 教程

使用正则表达式提取数字

我们可以使用正则表达式来从字符串中匹配数字。在MySQL中，提供了正则表达式的支持，我们可以使用REGEXP函数来进行匹配操作。

SELECT REGEXP_REPLACE('hello12world3', '[^0-9]+', '') AS number;

输出结果为：123

在上述代码中，使用REGEXP_REPLACE函数可以将所有非数字部分的字符替换为空字符串''，从而得到字符串中的数字部分。

SELECT REGEXP_REPLACE('hello12world3', '[^0-9]+', '') AS number;

输出结果为：123

上述代码中，使用了正则表达式[^0-9]+来匹配非数字部分的字符，^表示取反，意为不是数字的字符都进行匹配。+表示匹配一个或多个相连的非数字字符。

使用SUBSTRING函数提取数字

另一种提取字符串中数字的方法是使用SUBSTRING函数。该函数返回一个指定位置的子字符串。

SELECT SUBSTRING('hello12world3', 1, 2);

输出结果为：he

上述代码中，我们使用SUBSTRING函数取得了位置从1开始，长度为2的子字符串。

下面我们使用该函数结合循环语句从整个字符串中提取数字。

CREATE PROCEDURE extract_number(IN str VARCHAR(255), OUT number VARCHAR(255))
BEGIN
  DECLARE cur_str VARCHAR(255);
  DECLARE cur_char VARCHAR(1);
  DECLARE cur_pos INT DEFAULT 1;
  DECLARE str_len INT;

  SET str_len = LENGTH(str);
  SET number = '';

  label1: WHILE cur_pos <= str_len DO
    SET cur_char = SUBSTRING(str, cur_pos, 1);

    IF cur_char REGEXP '[0-9]+' THEN
      SET cur_str = cur_char;

      WHILE (cur_pos < str_len) AND (SUBSTRING(str, cur_pos + 1, 1) REGEXP '[0-9]+') DO
        SET cur_str = CONCAT(cur_str, SUBSTRING(str, cur_pos + 1, 1));
        SET cur_pos = cur_pos + 1;
      END WHILE;

      SET number = CONCAT(number, cur_str);
    END IF;

    SET cur_pos = cur_pos + 1;
  END WHILE label1;
END;

上述代码使用了MySQL中提供的存储过程，并结合循环语句逐字符遍历整个字符串。该存储过程的输入参数为字符串，输出参数为由该字符串中提取的数字构成的新字符串。

在存储过程中，首先获取字符串长度和初始位置。然后，通过循环逐字符获取当前字符，并分别进行判断。如果当前字符是数字，则将其与后面的数字字符拼接，直到数字串结束。最后将提取的数字字符串和当前位置拼接，继续进行下一轮循环。

使用UDF函数提取数字

虽然MySQL提供了正则表达式和SUBSTRING函数来提取数字，但是在实际使用中可能会遇到更加复杂的字符串，这时候很难通过简单的字符串操作来提取数字。因此，我们可以考虑自定义函数来完善这个过程。

DELIMITER //
CREATE FUNCTION extract_number(str varchar(255))   
    RETURNS varchar(255)   
BEGIN  
    DECLARE len INT DEFAULT length(str);  
    DECLARE numpos INT DEFAULT 1;  
    DECLARE numstr varchar(255) DEFAULT '';  
    WHILE(numpos<=len) DO  
        IF (substr(str,numpos,1) REGEXP '^[0-9]$') THEN  
               SET numstr = CONCAT(numstr,substr(str,numpos,1));  
        END IF;  
        SET numpos = numpos + 1;  
     END WHILE;  
     RETURN numstr;  
END//
DELIMITER ;

上述代码中，我们定义了一个名为extract_number的函数来提取字符串中的数字部分。该函数首先获取输入字符串的长度，并初始化数字串位置和数字字符串为空。然后，在循环中逐个遍历字符串中的字符。如果当前字符是数字，则将其拼接到数字字符串中；否则跳过该字符。最后，返回数字字符串。

使用该函数非常简单：