phpMyAdmin - mariaDB roman numerals function

Question

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.

score 0 · Answer 1 · answered Jul 02 '16 at 23:15

0

The best way to do this is to add another column that has a sortable equivalent of that string. And use non-SQL code to do the parsing and building of that column before inserting into the table.

answered Jul 02 '16 at 23:15

Rick James

135,179
13
127
222

Thank You Rick for answering to my question. I must say that normally I would do this the way You wrote but this was one time exception. I thought none will answer me and I already solved this on my own (yay!). I'll post the solution (what was I doing wrong) for future users. Also, do You know what's wrong with my question? Cause somebody gave me a minus for it. This is pretty stressful for new users when they get minus for new questions :p or maybe I shouldn't bother about it. :D – Thomas Kolasa Jul 04 '16 at 09:37
I often can't figure out why someone downvotes a question. I agree that it should be possible in SQL; I'll probably review it when you post it. How large can the roman numeral be? If it is just I,II,III,IIII, you could possibly use `LENGTH()`. – Rick James Jul 04 '16 at 17:18
Hi again @Rick, I added my solution - would You like to add something to it from Yourself? – Thomas Kolasa Jul 05 '16 at 12:58

score 0 · Accepted Answer · answered Jul 05 '16 at 12:56

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.

This might be 'better': `SET currentintnum = 0+convRomanNumeralSubFunction(SUBSTRING(numeral,counter, 1));` And add `DETERMINISTIC` before the `BEGIN`. — Rick James, Jul 06 '16 at 04:07

phpMyAdmin - mariaDB roman numerals function

2 Answers2