MySQL 自定义函数将罗马数字转为阿拉伯数字

2021-11-17 00:00:00 function arabic mysql roman-numerals

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

例如XXCVI

转换成它的阿拉伯数字等价物.至于我为什么需要它，这是一个很长的故事，我只是需要.

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

有人对我的函数有什么问题有任何提示，或者有更有效的方法将罗马数字字符串转换为阿拉伯数字吗?

DROP 函数如果存在`romeToArabic`$$CREATE DEFINER=`root`@`localhost` FUNCTION `romeToArabic`(roman_in VARCHAR(64)) 返回 int(11)开始声明数字 VARCHAR(2);声明 int_val INT;声明罗马 VARCHAR(64);DECLARE res INT;声明 no_more_rows 布尔值；声明 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;设置罗马 = UPPER(roman_in);设置资源= 0;如果存在 roman_numeral，则删除临时表；创建临时表 roman_numeral (`id` INT(8) NOT NULL AUTO_INCREMENT,`num` varchar(2) 默认为空，`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), ('我', 1);打开 roman_cur;SELECT FOUND_ROWS() INTO num_rows;the_loop:环形FETCH roman_cur INTO 数字，int_val；IF no_more_rows THEN CLOSE roman_cur;离开 the_loop;万一;WHILE INSTR(罗马，数字)= 1 DOSET res = res + int_val;设置罗马= SUBSTRING(罗马，长度(数字))；结束时；结束循环 the_loop;IF res＞0 那么返回资源；别的返回-1；万一;完$$

解决方案

不知道为什么你不工作，但谷歌搜索很快，我想出了这个链接:

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

CREATE FUNCTION fromRoman (inRoman varchar(15)) RETURNS integer DETERMINISTIC开始DECLARE 数字 CHAR(7) DEFAULT 'IVXLCDM';声明数字 TINYINT;DECLARE 前一个 INT DEFAULT 0;声明当前的INT；DECLARE sum INT DEFAULT 0;SET inRoman = UPPER(inRoman);虽然长度(罗马)>0 做SET digit := LOCATE(RIGHT(inRoman, 1), numeric) - 1;SET current := POW(10, FLOOR(digit/2)) * POW(5, MOD(digit, 2));SET sum := sum + POW(-1, current

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

e.g. XXCVI

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

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

相关文章