Using delimiters with multiple functions in MySql

Question

I have a table, book_chapters given with following sample data;

chapter_name | chapter_number
 A Dead Man  | LVI
 Imitation   | IX

As seen the chapter number are given in Roman numerals. The goal is to get the output in the following form;

chapter_name|chapter_number| cnt
A Dead Man  | LVI          | 56
Imitation   | IX           | 9

Here is my attempt at the code;

DELIMITER $$
DROP FUNCTION IF EXISTS primitive $$



CREATE FUNCTION primitive (r VARCHAR(15)) RETURNS INT DETERMINISTIC
BEGIN
     DECLARE result INT;

     IF r = 'I' THEN
       SET result =1;
        
    ELSEIF r = 'V' THEN
       SET result =5;
       
    ELSEIF r='X' THEN
       SET result=10;
       
    ELSEIF r = 'L' THEN 
        SET result= 50;
         
    ELSEIF r = 'C' THEN 
        SET result = 100; 
        
    ELSEIF r = 'D' THEN 
        SET result =500; 
        
    ELSEIF r = 'M' THEN 
        SET result = 1000;
    END IF;
    RETURN result;
END$$
DELIMITER;
DROP FUNCTION IF EXISTS chapter ;
DELIMITER $$ 


CREATE FUNCTION chapter(chaptername VARCHAR(20)) RETURNS INT DETERMINISTIC
BEGIN
     DECLARE val INT;
     DECLARE curr INT;
     DECLARE idx VARCHAR(20);
     DECLARE chapternum VARCHAR(20);
     SET val=0;
     SET curr=0;
     SET idx='';
     SET chapternum= chaptername;

     
     WHILE CHAR_LENGTH(chapternum)>0 DO
           SET idx=RIGHT(chapternum,1); /*traverse from the right side*/
           SET curr=primitive(idx);
           IF curr>=val THEN
              SET val= curr+val;
           ELSE
              SET val= val-curr;
           END IF;
           SET chapternum=LEFT(chapternum,CHAR_LENGTH(chapternum)-1);/*delete the rightmost char*/
     END WHILE;
     RETURN val;
END$$
DELIMITER;
DROP PROCEDURE IF EXISTS sortBookChapters;
DELIMITER $$

     
CREATE PROCEDURE sortBookChapters()
BEGIN 
 WITH CTE1 AS(
     
     SELECT chapter_name ,
            chapter_number AS chaptername
     FROM book_chapters
 )   
SELECT chapter_name, chaptername,chapter(chaptername) AS cnt
FROM CTE1;   

END $$

My logic of converting roman to decimal seems to be working, will be happy to get some feedback if there are errors. But I am getting following syntax error right now;

ERROR 1064 (42000) in the pre-written template: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';

CREATE FUNCTION chapter(chaptername VARCHAR(20)) RETURNS INT DETERMINISTIC B' at line 27

Can I get some help to debug this syntax error?

Normalise your data. Store numbers as integers. Display them however you like. — Strawberry
– Strawberry, Commented Oct 4, 2020 at 4:54

O. Jones · Accepted Answer · 2020-10-05 10:57:27Z

1

You gotta put your nonstandard delimiter ($$) after every stored function declaration.

You only have it after the last one in your example code.

And, it's IF ... ELSEIF ... ELSEIF ... END IF;. Don't use ELSE IF.

edited Oct 5, 2020 at 10:57

answered Oct 4, 2020 at 2:20

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jay Over a year ago

Appreciate your reply. I tried to put the delimiter after every stored function defn., as suggested. Please see the code above. In that case, I got new error; ` ERROR 1064 (42000) in the pre-written template: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 27`. Can you kindly advise?

jay Over a year ago

Than ks for the feedback. Please see the edited code. But this is giving. a new error message; ERROR 1065 (42000) in the pre-written template: Query was empty. Is there a way to debug it? Help is appreciated

L Q · Accepted Answer · 2020-10-07 07:55:20Z

0

No need to change the Delimiter.

This is your script running without errors:

DELIMITER $$

DROP FUNCTION IF EXISTS primitive $$

CREATE FUNCTION primitive (r VARCHAR(15)) RETURNS INT DETERMINISTIC
BEGIN
     DECLARE result INT;

     IF r = 'I' THEN
       SET result =1;
        
    ELSEIF r = 'V' THEN
       SET result =5;
       
    ELSEIF r='X' THEN
       SET result=10;
       
    ELSEIF r = 'L' THEN 
        SET result= 50;
         
    ELSEIF r = 'C' THEN 
        SET result = 100; 
        
    ELSEIF r = 'D' THEN 
        SET result =500; 
        
    ELSEIF r = 'M' THEN 
        SET result = 1000;
    END IF;
    RETURN result;
END$$

DROP FUNCTION IF EXISTS chapter $$

CREATE FUNCTION chapter(chaptername VARCHAR(20)) RETURNS INT DETERMINISTIC
BEGIN
     DECLARE val INT;
     DECLARE curr INT;
     DECLARE idx VARCHAR(20);
     DECLARE chapternum VARCHAR(20);
     SET val=0;
     SET curr=0;
     SET idx='';
     SET chapternum= chaptername;

     
     WHILE CHAR_LENGTH(chapternum)>0 DO
           SET idx=RIGHT(chapternum,1); /*traverse from the right side*/
           SET curr=primitive(idx);
           IF curr>=val THEN
              SET val= curr+val;
           ELSE
              SET val= val-curr;
           END IF;
           SET chapternum=LEFT(chapternum,CHAR_LENGTH(chapternum)-1);/*delete the rightmost char*/
     END WHILE;
     RETURN val;
END$$

DROP PROCEDURE IF EXISTS sortBookChapters $$
     
CREATE PROCEDURE sortBookChapters()
BEGIN 
     WITH CTE1 AS(
         
         SELECT chapter_name ,
                chapter_number AS chaptername
         FROM book_chapters
     )   
    SELECT chapter_name, chaptername,chapter(chaptername) AS cnt
    FROM CTE1;   
    
END $$
DELIMITER ;

edited Oct 7, 2020 at 7:55

answered Oct 6, 2020 at 8:05

L Q

814 bronze badges

3 Comments

jay Over a year ago

@L Q. Appreciate your reply. But isn't that's what the mysql documentation says, to change the delimiters. Being a beginner, I am trying to follow this link on how to implement stored functions in MySql. mysqltutorial.org/mysql-stored-function. Can you kindly provide thoughts?

L Q Over a year ago

The standard MySQL delimiter is (;) which is also used to end the statements in Triggers, Functions and Procedures, so for the client to send the create function block complete as one to the server you should change the delimiter (if you are sending a script to create triggers, stored functions or procedures) but you need to change it only once at the beginning and set it back at the end of the script, I've updated your script in my answer.

jay Over a year ago

@L Q Apologies I could not reply earlier. As suggested, I used the above provided code, change delimiter at the start and set it back at the end. This has provided a new error message;

ERROR 1064 (42000) in the pre-written template: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$ delimiter' at line 1

. I'm not being able to comprehend, at which line its referring to. I want to clarify that MySql version is 8.0.21. Is it possible, you can provide some insight.your advice is appreciated.

Collectives™ on Stack Overflow

Using delimiters with multiple functions in MySql

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related