在SQL中解析字符串中的整数

不幸的是,你不能在MySQL中独自完成这项工作。MySQL有正则表达式匹配功能,但没有正则表达式替换功能。最好的选择是在PHP中使用正则表达式来执行替换。 _{( Source )}

$sql = "SELECT `id`, `myMixedColumn` FROM `myTable` "
     . "WHERE `myMixedColumn` NOT RLIKE '^[[:digit:]]+$'";
$r = mysql_query($sql);

$updates = array();
while ($row = mysql_fetch_assoc($r)) {
    $updates = sprintf("UPDATE `myTable` SET `myIntField` = %s WHERE `id` = %d",
        preg_replace("@\\D@", "", $row['myMixedColumn']),
        $row['id']
    );
}

如果你真的想在SQL中实现它,只是为了不在PHP中实现,你可以制作一个小函数,直到MySQL实现一个正则表达式替换,但我不会对性能打赌。

只有当字母在开头,并且除了[a-zA-Z]之外没有其他字符时,这样的事情才会起作用。你必须检查它如何在不同的字符集中运行。

CREATE FUNCTION last_letter(s VARCHAR(100)) RETURNS INT
BEGIN
  DECLARE last, current INT default 0;
  DECLARE letter_a INT;
  DECLARE letter_z INT;
  DECLARE letter_iter INT;
  SELECT ord('a') INTO letter_a;
  SELECT ord('z') INTO letter_z;
  SET letter_iter = letter_a;
  # Will loop for all letters a to z
  WHILE letter_iter <= letter_z DO
    # Will get the last case-insensitive occurrence of a letter
    SELECT LOCATE(CHAR(letter_iter), REVERSE(LOWER(s))) INTO current;
    IF current > 0 THEN
      SELECT LENGTH(s) - current + 1 INTO current;
    END IF;
    # Was that the rightmost letter?
    IF current > last THEN
      SET last = current;
    END IF;
    SET letter_iter = letter_iter + 1;
  END WHILE;
  # Return the max we found
  RETURN last;
END; //

然后得到整数值:

UPDATE test_table SET int_result = 
  CAST(SUBSTR(str_value, last_letter(str_value) + 1) AS SIGNED);