title: MySQL Custom Function to Turn Roman Numeral Into Arabic
body: <p>Ok, I need a MySQL Function that will convert a Roman Numeral String:</p>
  
  <p>e.g. XXCVI</p>
  
  <p>Into its Arabic numbering equivalent. Its a long story as to why I need it, I just do.</p>
  
  <p>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)</p>
  
  <p>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?</p>
  
  <pre><code>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 &gt; 0 THEN
      RETURN res;
    ELSE
      RETURN -1;
    END IF;
  END$$
  </code></pre>