[FIXED] phpMyAdmin - mariaDB roman numerals function

Issue

can anybody help me with my sorting function - seriously I don't know how can I make it work as supposed to. :( Database is in MariaDB in Xampp. I use phpMyAdmin to execute the query.

DELIMITER $$

DROP FUNCTION IF EXISTS convRomanNumeral$$

CREATE FUNCTION convRomanNumeral (numeral CHAR(4))
  RETURNS INT
BEGIN
  DECLARE intnum INT;
    CASE numeral
      WHEN "I" THEN intnum = 1;
      WHEN "II" THEN intnum = 2;
    END CASE;
  RETURN intnum;
END;

$$

SET @iteration = -1;

UPDATE `st0gk_docman_documents` 
    SET created_on = DATE('2016-06-14') + INTERVAL(@iteration := @iteration + 1) SECOND
    WHERE `docman_category_id` = 141 ORDER BY convRomanNumeral(SUBSTRING(SUBSTRING_INDEX(title,'/',1),' ',-2) ASC, SUBSTRING_INDEX(title,'/',-2)+0 ASC;

So what I want to achieve is to sort documents by title. Example titles are:

• Document Nr I/36/2006
• Document Nr II/36/2006
• Document Nr I/32/2006
• Document Nr II/19/2006

After sorting them by first Roman number and then by second Arabic number I want to update the date. Code below for updating by only second Arabic number works properly:

SET @iteration = -1;

UPDATE `st0gk_docman_documents` 
    SET created_on = DATE('2016-06-14') + INTERVAL(@iteration := @iteration + 1) SECOND
    WHERE `docman_category_id` = 141 ORDER BY SUBSTRING_INDEX(title,'/',-2)+0 ASC;

I would like to use CASE to return proper variable for Roman values. I know it's not perfect but I can't even make the CASE and FUNCTION work. What I am doing wrong? All suggestions are welcome.

Solution

First mistake that I was making it was trying to execute the whole query at once... After taking the first lodge out of the way the debugging seemed way simpler. :D

So I created my case function to convert Roman numerals:

DELIMITER $$

DROP FUNCTION IF EXISTS convRomanNumeralSubFunction$$
CREATE FUNCTION convRomanNumeralSubFunction (numeral CHAR(1))
  RETURNS INT
BEGIN
  DECLARE intnum INT;
    CASE numeral
      WHEN "I" THEN SELECT 1 INTO intnum;
      WHEN "X" THEN SELECT 10 INTO intnum;
      WHEN "C" THEN SELECT 100 INTO intnum;
      WHEN "M" THEN SELECT 1000 INTO intnum;
      WHEN "V" THEN SELECT 5 INTO intnum;
      WHEN "L" THEN SELECT 50 INTO intnum;
      WHEN "D" THEN SELECT 500 INTO intnum;
    END CASE;
  RETURN intnum;
END;

$$

After that I declared the second function needed for conversion. I don't know if You can declare function inside function... and I didn't want to waste more time on this. For sure You can declare Function inside Procedure. Anyhow. WARNING: This function is not proof of BAD numerals like IIX. Numerals like that or will be badly counted. Also AXI will not count.

DELIMITER $$

DROP FUNCTION IF EXISTS convRomanNumeral$$
CREATE FUNCTION convRomanNumeral (numeral CHAR(10))
  RETURNS INT
BEGIN
  DECLARE currentintnum, previntnum, intnum, counter, numerallength INT;
  SET numerallength = LENGTH(numeral);
  SET counter = numerallength;
  SET intnum = 0;
  SET previntnum = 0;
  WHILE counter > 0 DO
      SET currentintnum = CAST(convRomanNumeralSubFunction(SUBSTRING(numeral,counter, 1)) as integer);
      IF currentintnum < previntnum THEN
        SET intnum = intnum - currentintnum;
      ELSE 
        SET intnum = intnum + currentintnum;
      END IF;
      SET previntnum = currentintnum;
      SET counter = counter - 1;
  END WHILE;
  RETURN intnum;
END;

$$

So that's it. Now You can convert all kind of Roman numerals and sort them up. Use this to test the conversion:

SELECT convRomanNumeral("XIX");

This is example sorting code that I in the end used:

SET @iteration = -1;

UPDATE `st0gk_docman_documents` 
    SET created_on = DATE('2016-06-07') + INTERVAL(@iteration := @iteration + 1) SECOND
    WHERE `docman_category_id` = 67 ORDER BY convRomanNumeralBreak(SUBSTRING_INDEX(SUBSTRING_INDEX(title,'/',1),' ',-1)) ASC, SUBSTRING_INDEX(title,'/',-2)+0 ASC;

Also one more thing - if You'll try to excecute this on mySQL then You have to fix this line:

SET currentintnum = CAST(convRomanNumeralSubFunction(SUBSTRING(numeral,counter, 1)) as integer);

into this:

SET currentintnum = CAST(convRomanNumeralSubFunction(SUBSTRING(numeral,counter, 1)) as SIGNED);

This code could be improved but as the @Rick James stated this should be done differently - not in as db update but in different table structure and sorting mechanism.

Answered By - Thomas Kolasa