Translate from Roman Numerals back to decimal. Legal range is 0 to 3999 (that is, N to MMMCMXCIX) You can use IV or IIII for 4. You could even use a string of twenty I’s for 20, but there’s a string limit of 15, since that’s the length of the biggest well-formed numeral below 3999.
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
