MySQL自定义函数将罗马数字转换成阿拉伯语 [英] MySQL Custom Function to Turn Roman Numeral Into Arabic

问题描述

好吧，我需要一个可以转换罗马数字字符串的MySQL函数:

Ok, I need a MySQL Function that will convert a Roman Numeral String:

例如XXCVI

转换为其等效的阿拉伯数字.关于我为什么需要它，我只是做了一个很长的故事.

Into its Arabic numbering equivalent. Its a long story as to why I need it, I just do.

基于某人发布的PHP函数，我创建了以下MySQL函数，但是它似乎无休止地运行，我不确定为什么. (我可能太累了)

Based on a PHP function that someone posted, I created the following MySQL Function, but it seems to be running endlessly and I'm not sure why. (I might just be too tired)

有人对我的功能有什么疑问吗?或者有更有效的方法将罗马数字字符串转换为阿拉伯数字?

Anybody have any hints as to what's wrong with my function, or have a more efficient way to convert a roman numeral string into an Arabic number?

DROP FUNCTION IF EXISTS `romeToArabic`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `romeToArabic`(roman_in VARCHAR(64)) RETURNS int(11)
BEGIN
  DECLARE numeral VARCHAR(2);
  DECLARE int_val INT;
  DECLARE roman VARCHAR(64);
  DECLARE res INT;
  DECLARE no_more_rows BOOLEAN;
  DECLARE num_rows INT DEFAULT 0;
  DECLARE roman_cur CURSOR FOR SELECT num, val FROM roman_numeral ORDER BY id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
  SET roman = UPPER(roman_in);
  SET res = 0;

  DROP TEMPORARY TABLE IF EXISTS roman_numeral;
  CREATE TEMPORARY TABLE roman_numeral (
      `id` INT(8) NOT NULL AUTO_INCREMENT,
      `num` varchar(2) DEFAULT NULL,
      `val` int(8) NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;

  INSERT INTO roman_numeral (num, val) VALUES ('M', 1000), ('CM', 900), ('D', 500), ('CD', 400), ('C', 100), ('XC', 90), ('L', 50), ('XL', 40), ('X', 10), ('IX', 9), ('V', 5), ('IV', 4), ('I', 1);

  OPEN roman_cur;
  SELECT FOUND_ROWS() INTO num_rows;

 the_loop:
  LOOP
    FETCH  roman_cur INTO   numeral, int_val;
    IF no_more_rows THEN CLOSE roman_cur;
      LEAVE the_loop;
    END IF;

    WHILE INSTR(roman, numeral) = 1 DO
      SET res = res + int_val;
      SET roman = SUBSTRING(roman, LENGTH(numeral));
    END WHILE;

  END LOOP the_loop;
  IF res > 0 THEN
    RETURN res;
  ELSE
    RETURN -1;
  END IF;
END$$

推荐答案

不确定您的为何不起作用，但谷歌搜索速度很快，我想出了以下链接:

Not sure why your isnt working but googling was fast and I came up with this link:

http://forge.mysql.com/tools/tool.php? id = 107

CREATE FUNCTION fromRoman (inRoman varchar(15)) RETURNS integer DETERMINISTIC
BEGIN

    DECLARE numeral CHAR(7) DEFAULT 'IVXLCDM';

    DECLARE digit TINYINT;
    DECLARE previous INT DEFAULT 0;
    DECLARE current INT;
    DECLARE sum INT DEFAULT 0;

    SET inRoman = UPPER(inRoman);

    WHILE LENGTH(inRoman) > 0 DO
        SET digit := LOCATE(RIGHT(inRoman, 1), numeral) - 1;
        SET current := POW(10, FLOOR(digit / 2)) * POW(5, MOD(digit, 2));
        SET sum := sum + POW(-1, current < previous) * current;
        SET previous := current;
        SET inRoman = LEFT(inRoman, LENGTH(inRoman) - 1);
    END WHILE;

    RETURN sum;
END

这篇关于MySQL自定义函数将罗马数字转换成阿拉伯语的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！